Some differences between Mysql and SQL Server Analysis of _mysql

1, MySQL support enum, and set type, SQL Server does not support
2, MySQL does not support nchar,nvarchar,ntext type
3, the MySQL increment statement is auto_increment, and MSSQL is identity (1,1)
4, MSSQL default Everywhere table creation statement default value is ((0)), and in MySQL inside is not allowed with two parentheses
5. mysql needs to specify storage type for table
6, the MSSQL identifier is [],[type] means that he is different from the keyword, but MySQL is ', that is, key 1 to the left of the symbol
7, MSSQL support GETDATE () method to get the current time date, but MySQL can be divided into date type and time type, get the current date is cur_date (), the current full time is now () function
8, MSSQL does not support the replace into statement, but in the latest sql20008, also support the merge syntax
9, MySQL support insert INTO table1 set t1 = ', t2 = ', but MSSQL does not support this write
10. mysql supports insert into TABL1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1,1), (1,1)
One MSSQL does not support limit statements, is very regrettable, can only use top replace LIMT 0,n,row_number () over () function to replace limit n,m
12, MySQL in the creation of a table to each table to specify a storage engine type, and MSSQL only support a storage engine
13, MySQL does not support the default value of the current time datetime type (MSSQL is easy to do), in MySQL inside is using the timestamp type
14, MSSQL inside check whether there is this table and then delete, need to do so: if exists (SELECT * from dbo.sysobjects where Id=id (n ' uc_newpm ') and OBJECTPROPERTY (ID, n ' IsU Sertable ') = 1) but only the DROP TABLE IF EXISTS cdb_forums is required in MySQL;
15, the MySQL support unsigned integer, then more than no support for the unsigned MSSQL can be more than one times the maximum number of storage
16, MySQL does not support the use of very convenient varchar (max) type, this type in MSSQL can do both general data storage, can do BLOB data storage
17, MySQL create nonclustered index only need to create a table when the designated as key on the line, such as: Key Displayorder (Fid,displayorder) in the MSSQL must be:

Copy Code code as follows:

Create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers (username ASC, AppID ASC)

18, MySQL text field type does not allow a default value
19. The total field length of a table in MySQL is no more than 65XXX.
20, a very superficial difference is that the installation of MySQL is particularly simple, and file size is only about 23M (5.5.23), compared to Microsoft this behemoth, installation progress is simply ...
21, MySQL management tools have a few relatively good, mysql_front, and the official that suite, but there is no ssms easy to use, this is a big drawback of MySQL.
22, the MySQL stored procedures only appear in the latest version, stability and performance may not be as MSSQL.
23, the same load pressure, MySQL to consume less CPU and memory, MSSQL is indeed very resource-consuming.
24, PHP connection MySQL and MSSQL the same way, just want to replace the function of the MySQL can be, if it is PDO way only to replace the MySQL can.
25, MySQL support date,time,year type, MSSQL to 2008 only to support date and time.

Appendix: The difference between MySQL and MSSQL paging

has been using MySQL, although compared to MSSQL this huge database system MySQL is slim, but it is not inferior. Here are the differences between the two separate pages in the database
Example 1, remove the first 10

Copy Code code as follows:

SELECT * FROM table LIMIT 10;

In the MSSQL

Copy Code code as follows:

SELECT Top * FROM table

Example 2, each page 10, remove the third page
In MySQL

Copy Code code as follows:

SELECT * FROM table LIMIT 20,10

In the MSSQL

Copy Code code as follows:

SELECT Top * FROM table WHERE ID not in (
SELECT Top ID from table order by ID DESC
) Order by ID DESC;

As can be seen from the above example, in MySQL pagination with the limit keyword, if it is limit 10 to take the first 10, if it is limit 10,10 represents offset 10 to take the first 10 records. In MSSQL with the top keyword, if you take only the first N records directly to the N, but if the paging is a bit troublesome.