Difference between ms SQL Server and MySQL

Recently, I am working on converting ms SQL Server to MySQL. I have summarized some experiences and shared them with you. At the same time, these will be constantly updated. I hope you can add more information.

1. MySQL supports enum, set, and SQL Server.

2 MySQL does not support nchar, nvarchar, and ntext

3. the incremental Statement of MySQL is AUTO_INCREMENT, while that of ms SQL is identity)

4 ms SQL by default, the default value of the everywhere table creation statement is (0), and in MySQL it is not allowed with parentheses

5 MySQL needs to specify the storage type for the table

6 ms SQL identifier is [], [type], it is different from the keyword, but MySQL is ', that is, the symbol on the left of the key 1

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

8 ms SQL does not support replace into statements, but in the latest sql20008, also supports merge syntax

9 MySQL supports insert into table1 set t1 = '', t2 ='', but ms SQL does not support this write.

10 MySQL supports insert into tabl1 values)

11 ms SQL does not support the limit statement, is very sorry, can only replace limt 0, N, row_number () over () function instead of limit N, M

12 when creating a table, MySQL must specify a storage engine type for each 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 it is the timestamp type

14 ms SQL inside check whether there is this table and then delete, need to be like this:

If exists (select * from dbo. sysobjects where id = object_id (Nuc_newpm) and OBJECTPROPERTY (id, NIsUserTable) = 1) but in MySQL, you only need to drop table if exists cdb_forums;

 

However, in MySQL, you only need to drop table if exists cdb_forums;

15 MySQL supports unsigned integers, so it can store up to twice the maximum number of unsigned ms SQL statements.

16 MySQL does not support the varchar (max) type that is very convenient in ms SQL. This type can be used in ms SQL to store both general data and blob data.

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) must be:

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

 

18 The MySQL text field type cannot have default values

The total field length of a table in 19MySQL cannot exceed 65XXX.

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

There are several good 21 MySQL management tools, MySQL_front, and the official suite, but they are not easy to use SSMS, which is a major disadvantage of MySQL.

22MySQL stored procedures only appear in the latest version, and the stability and performance may be inferior to ms SQL.

23 The same load pressure, MySQL to consume less CPU and memory, ms SQL is indeed very resource-consuming.

24php connects to MySQL and ms SQL in similar ways. You only need to replace the MySQL of the function with ms SQL.

25MySQL supports date, time, year type, ms SQL to 2008 only support date and time.