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 them.
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 The default value of MS SQL's table creation statement is ((0)), but in MySQL, two parentheses are not allowed.
5 MySQL needs to specify the storage type for the table
6 MS SQL identifier is [], [type] which means it is different from keywords, but MySQL is `, which is the symbol to the left 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. The current 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, the 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 To check whether this table exists in MS SQL and then delete 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 only DROP TABLE IF EXISTS cdb_forums;
But in MySQL you only need DROP TABLE IF EXISTS cdb_forums;
15 MySQL supports unsigned integers, so it can store twice as many maximum numbers as MS SQL, which does not support unsigned integers.
16 MySQL does not support the very convenient varchar(max) type in MS SQL. This type can be used for both general data storage and blob data storage in MS SQL.
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.
20 A 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 has several better management tools, including 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.