MySQL vs SQLServer

[kenttanl]

数据类型映射关系

No | SQL Server | MySQL | Range | Storage(Bytes) | Comment -- | -- | -- | -- | -- | -- 1 | INT | INT | MS SQL : -2^31~2^31-1MySQL : 0~2^32-1 | 4 |   2 | TINYINT | TINYINT | MS SQL : 0~255MySQL : 128~127 | 1 | UNSIGNED flag set in MySQL. 3 | SMALLINT | SMALLINT | MS SQL : -2^15~2^15-1MySQL : 0~2^16-1 | 2 |   4 | BIGINT | BIGINT | MS SQL : -2^63~2^63-1MySQL : 0~2^64-1 | 8 |   5 | BIT | TINYINT(1) | 0, 1, NULL | MS SQL : 1~2MySQL : 1 | Microsoft SQL Server Type Mapping 6 | FLOAT(n) | FLOAT(p) | MS SQL-FLOAT(n)的n值single-precision:1-24double-precision:0-23MySQL-FLOAT(p)的p值single-precision:25-53double-precision:24-53 | single-precision:4double-precision:8 | Precision value is used for storage size in both. 7 | REAL | FLOAT | float(24) | 4 |   8 | NUMERIC(p,s) | DECIMAL(M,D) | MS SQL-NUMERIC(p,s):(有效位數,小數位數)p:1-38MySQL-DECIMAL(M,D):(有效位數,小數位數)M:1-65 | MS SQLPrecisionNumber of Bytes1-9510-19920-281329-3817MySQLLeftover DigitsNumber of Bytes0 01–213–4 25–637–9 4 | 1-9 | 5 | 10-19 | 9 | 20-28 | 13 | 29-38 | 17 | 0 | 0 | 1–2 | 1 | 3–4 | 2 | 5–6 | 3 | 7–9 | 4 | MySQLFor example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require 4 bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3 bytes for the remaining five digits. The six fractional digits require 3 bytes.https://dev.mysql.com/doc/refman/8.0/en/precision-math-decimal-characteristics.html 9 | DECIMAL(p,s) | DECIMAL(M,D) | MS SQL-DECIMAL同No.8的NUMERICMySQL-DECIMAL同No.8的DECIMAL |   |   10 | MONEY | DECIMAL | MS SQL-MONEY-922,337,203,685,477.5808 到 922,337,203,685,477.5807MySQL-同No.8的DECIMAL | 8 |   11 | SMALLMONEY | DECIMAL | MS SQL-SMALLMONEY- 214,748.3648 到 214,748.3647MySQL-同No.8的DECIMAL | 4 |   12 | CHAR(n) | CHAR(n)/LONGTEXT | MS SQL-CHAR(n)n:1-8000MySQL-CHAR(n)n:0-255 | CHAR: n個位元組MySQL-LONGTEXTL + 4 bytes,  L < 2^32 | MySQLDepending on its length. MySQL Server 5.6 and higher can have CHAR columns with a length up to 255 characters. Anything larger is migrated as LONGTEXT.LONGTEXT: L represents the actual length in bytes of a given string value. 13 | NCHAR(n) | CHAR(n) CHARACTER SET utf8mb4/LONGTEXT | MS SQL-NCHAR(n)n:1-4000MySQL-CHAR(n)n:0-255 | MS SQL-NCHARn*2 個位元組MySQL-CHARn個位元組 | MySQLDepending on its length. MySQL Server 5.6 and higher can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, a character set of strings depends on the column character set instead of the data type.NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release.Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguoushttps://bugs.mysql.com/bug.php?id=76529 14 | VARCHAR(n) | VARCHAR(n)/MEDIUMTEXT/LONGTEXT | MS SQL-VARCHAR(n)n:1-8000MySQL-VARCHAR(n)n:0-65535 | MS SQL-VARCHAR實際儲存的位元組+ 2 位元組MySQL-VARCHARn<255,實際儲存的位元組+ 1 位元組n>=255,實際儲存的位元組+ 2 位元組MySQL-MEDIUMTEXTL + 3 bytes, L < 2^24MySQL-LONGTEXTL + 4 bytes,  L < 2^32 | MySQLDepending on its length. MySQL Server 5.6 and higher can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types.MEDIUMTEXT/LONGTEXT: L represents the actual length in bytes of a given string value. 15 | NVARCHAR(n) | VARCHAR(N) CHARACTER SET utf8mb4MEDIUMTEXT/LONGTEXT | MS SQL-NVARCHAR(n)n:1-4000MySQL-VARCHAR(n)n:0-65535 | MS SQL-NVARCHAR實際儲存的位元組*2+ 2 位元組MySQL-VARCHARn<255,實際儲存的位元組+ 1 位元組n>=255,實際儲存的位元組+ 2 位元組MySQL-同No.14的MEDIUMTEXT/LONGTEXT | Depending on its length. MySQL Server 5.6 and higher can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, a character set of strings depends on the column character set instead of the data type. 16 | DATE | DATE | MS SQL0001-01-01 to 9999-12-31MySQL1000-01-01 to 9999-12-31 | 3 |   17 | DATETIME(p) | DATETIME(p) | MS SQL1753-1-1 to 9999-12-31 00:00:00.000 到 23:59:59.997MySQL1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 | MS SQL8MySQLMySQL 5.6.4以前:8 bytesMySQL 5.6.4以後:5 bytes + fractional seconds storageFractional Seconds PrecisionStorage Required00 bytes1, 21 byte3, 42 bytes5, 63 bytes | 0 | 0 bytes | 1, 2 | 1 byte | 3, 4 | 2 bytes | 5, 6 | 3 bytes | MS SQL毫秒的精準度:Rounded to increments of .000, .003, or .007 secondsMySQLAs of MySQL 5.6.4, storage for YEAR and DATE remains unchanged. However, TIME, DATETIME, and TIMESTAMP are represented differently. DATETIME is packed more efficiently, requiring 5 rather than 8 bytes for the nonfractional part, and all three parts have a fractional part that requires from 0 to 3 bytes, depending on the fractional seconds precision of stored values. 18 | DATETIME2(p) | DATETIME(p) | MS SQL-DATETIME20001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999MySQL-同No.17的DATETIME | MS SQLp<3: 6 bytesp=3 or p=4: 7 bytesother: 8 bytes. |   19 | SMALLDATETIME | DATETIME | MS SQL-SMALLDATETIME1900-01-01 00:00:00 to 2079-06-06 23:59:59MySQL-同No.17的DATETIME | MS SQL4 |   20 | DATETIMEOFFSET | DATETIME | MS SQL-DATETIMEOFFSET0001-01-01 00:00:00.00.0000000 to 9999-12-31 23:59:59.9999999MySQL-同No.17的DATETIME | MS SQL10 bytes, fixed is the default with the default of 100ns fractional second precision. |   21 | TIME(p) | TIME(p) | MS SQL-TIME00:00:00.0000000 to 23:59:59.9999999MySQL-TIME-838:59:59.000000 to 838:59:59.000000 | MS SQL5 bytes, fixed, is the default with the default of 100ns fractional second precision.MySQLMySQL 5.6.4以前:3 bytesMySQL 5.6.4以後:3 bytes + fractional seconds storageFractional Seconds PrecisionStorage Required00 bytes1, 21 byte3, 42 bytes5, 63 bytes | 0 | 0 bytes | 1, 2 | 1 byte | 3, 4 | 2 bytes | 5, 6 | 3 bytes | MySQLFor example, TIME(0), TIME(2), TIME(4), and TIME(6) use 3, 4, 5, and 6 bytes, respectively. TIME and TIME(0) are equivalent and require the same storage. 22 | TIMESTAMP | TIMESTAMP |   |   |   23 | ROWVERSION | TIMESTAMP |   |   |   24 | BINARY | BINARY/MEDIUMBLOB/LONGBLOB |   |   | Depending on its length. 25 | VARBINARY | VARBINARY/MEDIUMBLOB/LONGBLOB |   |   | Depending on its length. 26 | TEXT | VARCHAR/MEDIUMTEXT/LONGTEXT |   |   | Depending on its length. 27 | NTEXT | VARCHAR/MEDIUMTEXT/LONGTEXT |   |   | Depending on its length. 28 | IMAGE | TINYBLOB/MEDIUMBLOB/LONGBLOB |   |   | Depending on its length. 29 | SQL_VARIANT | not migrated |   |   | There is not specific support for this data type. 30 | TABLE | not migrated |   |   | There is not specific support for this data type. 31 | HIERARCHYID | not migrated |   |   | There is not specific support for this data type. 32 | UNIQUEIDENTIFIER | VARCHAR(64) |   |   | A unique flag set in MySQL. There is not specific support for inserting unique identifier values. 33 | SYSNAME | VARCHAR(160) |   |   |   34 | XML | TEXT |   |   |  

[kenttanl]

简单语法对比

操作类型 | MySQL | SQL Server | Note -- | -- | -- | -- 表中添加列（DDL） | ALTER TABLE tablename ADD COLUMN columnname  type[first/after columnname] | ALTER TABLE tablename ADD columnname type | 在MySQL中可以指定新列的位置，SQL Server只能默认加在最后 表中添加索引（DDL） | CREATE INDEX indexname ON tablename(columnname) | CREATE INDEX indexname ON tablename(columnname) |   当列上有索引时，修改列属性（DDL） | ALTER TABLE tablename MODIFY COLUMN columnname type | DROP INDEX indexname ON tablenameALTER TABLE tablename ALTER COLUMN coluname typeCREATE INDEX indexname ON tablename(columnname) | 在MySQL中，某个列有索引，如果我们想修改这个列，是直接进行修改；在SQL Server中，某个列有索引，如果我们想修改这个列，必须先删除索引，修改列，最后再将索引加回 修改表中列顺序（DDL） | ALTER TABLE tablename MODIFY columnname type first/after | 不支持 |   重命名表（DDL） | ALTER TABLE tablename RENAME TO newtable_name | EXEC SP_RENAME 'tablename','newtablename' |   同时更新多表数据（DML） | UPDATE tablenamea a,tablenameb b SET a.columnname=xxx,b.columnname=yyy WHERE a.xx=b.xx | 不支持 |   查询前几条数据（DML） | SELECT * FROM tablename LIMIT 10; | SELECT TOP 10 * FROM tablename |   数据组合成新数据（DML） | SELECT CONCAT('DROP TABLE ','table_name',';') FROM tables WHERE table_schema='test1' | SELECT 'DROP TABLE '+name +';' FROM test1.sys.tables |  

[kenttanl]

关于 CHAR/VARCHAR 的差异

在 MySQL 中，仅当列定义为 VARCHAR，并且插入数据时尾部带有空格，将该值作为 WHERE 条件时需要带上对应的空格，否则不会返回数据。

其他三种情况（定义为 CHAR 且插入数据尾部不带空格，定义为 CHAR 且插入数据尾部带空格，定义为 VARCHAR 且插入数据尾部不带空格），将该值作为 WHERE 条件时仅需传入实际数据（如果插入数据时带有空格，作为条件查询时也无需带上空格）。如果传入了带有尾部空格的数据作为查询条件，不会有结果返回。

在SQL Server中，不管是四种情况中哪一种，均可只传入实际数据作为 WHERE 条件，并且得到数据返回

[kenttanl]

简单差异描述

对比点 | MySQL | SQL Sever -- | -- | -- 兼容性支持 | 支持多平台操作系统 | 虽然提供多平台，但是最主要还是在Windows，非Windows平台某些功能缺失 开发语言支持 | 支持多种开发语言 | 支持多种开发语言 存储引擎 | 支持多种存储引擎，当前推荐InnoDB | 没有存储引擎概念 成本因素 | 开源免费 | 商业软件 IDE工具 | 多种工具 | 以SSMS为主，丰富的功能 安全因素 | 服务运行时文件可被其他进程访问修改 | 服务运行时无法被其他进程访问修改，相对更安全 备份还原 | 相对繁琐，但是更灵活 | 非常简单，但是备份最小单位是数据库 开发维护 | Oracle。同时存在其他分支 | 微软 SQL语法 | 简单易用 | 相对更复杂 多国语言支持 | 只有英语版本 | 多国语言均支持

[kenttanl]

【强制要求】在 MySQL 中仅需选择 VARCHAR 类型，禁止选择 NCHAR 或者 NVARCHAR 类型

解读：因为我们已经设置数据库的字符集为 UTF8mb4，已经能存储多国语言了。一旦我们设置字段类型为 NCHAR 或者NVARCHAR, MySQL 会自动将这个列转为 CHAR 或者 VARCHAR，同时将该列字符集转成 UTF8mb3，也即常规的 UTF8, 该列的字符集就会与数据库默认字符集相异。

[kenttanl]