The difference between MS SQL Server and MySQL

Recently I have been working on converting MS SQL Server to MySQL. I have summarized some experience and share it with you. At the same time, these will be continuously updated. I also hope you can add more. 1 MySQL supports enum and set types, but SQL Server does not support

　 2 MySQL does not support nchar, nvarchar, ntext types

3 MySQL’s increment statement is AUTO_INCREMENT, while MS SQL is identity(1,1)

4 MS SQL The default value of the table creation statement is ((0)), and two parentheses are not allowed in MySQL

　5 MySQL needs to specify the storage type for the table

　6 MS SQL identifiers are [], [type ] means that it is different from keywords, but MySQL is `, which is the symbol on the left side of button 1

　7 MS SQL supports the getdate() method to obtain the current time and date, but MySQL can be divided into date type and time type to obtain the current time and date. The date is cur_date(), and the current complete time is the now() function

　8 MS SQL does not support the replace into statement, but in the latest sql20008, merge syntax is also supported

　9 MySQL supports insert into table1 set t1 = '', t2 = '' , but MS SQL does not support writing this way

　10 MySQL supports insert into tabl1 values ​​(1,1), (1,1), (1,1), (1,1), (1,1) , (1,1), (1,1)

　11 MS SQL does not support the limit statement, which is very regrettable. You can only use top to replace limit 0,N, and the row_number() over() function to replace limit N,M

　12 MySQL must specify a storage engine type for each table when creating a table, while MS SQL only supports one storage engine

　13 MySQL does not support the datetime type whose default value is the current time (MS SQL is easy to do), In MySQL, the timestamp type is used

14 In MS SQL, to check whether there is this table before deleting it, you need to do this:

if exists (select * from dbo.sysobjects where id = object_id(N'uc_newpm') and OBJECTPROPERTY(id, N'IsUserTable')=1) But in MySQL, you only need DROP TABLE IF EXISTS cdb_forums;



But in MySQL, you only need DROP TABLE IF EXISTS cdb_forums;

　15 MySQL supports unsigned integers, so why not Supporting unsigned MS SQL can double the maximum number storage

　16 MySQL does not support the varchar(max) type, which is very convenient to use in MS SQL. This type can be used for general data storage in MS SQL. You can also do blob data storage

　17 To create a non-clustered index in MySQL, you only need to specify it as the key when creating the table, for example: KEY displayorder (fid, displayorder) In MS SQL you must:

create unique nonclustered indexindex_uc_protectedmembers_username_appid on dbo .uc_protectedmembers(username asc,appid asc)



　18 MySQL text field type does not allow default values

　19The total field length of a MySQL table does not exceed 65XXX.

　20A very superficial difference is that the installation of MySQL is very simple, and the file size is only 110M (non-installation version). Compared with the behemoth Microsoft, the installation progress is simply...

　21MySQL management tools are There are a few better ones, MySQL_front, and the official suite, but they are not as easy to use as SSMS. This is a big shortcoming of MySQL.

　22MySQL’s stored procedures only appear in the latest version, and the stability and performance may not be as good as MS SQL.

　23 Under the same load pressure, MySQL consumes less CPU and memory, while MS SQL is indeed very resource-consuming.

　24php has similar methods of connecting to MySQL and MS SQL. You only need to replace the MySQL function with MS SQL.

　25MySQL supports date, time, and year types. MS SQL only supports date and time in 2008.

The above is the difference between MS SQL Server and MySQL. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!