Difference: 1. mysql supports enum and set types, but sql server does not support it; 2. mysql’s increment statement is “AUTO_INCREMENT”, while sql server is “identity(1,1)”; 3. sql server The limit statement is not supported.
(Recommended tutorial: mysql video tutorial)
mysql supports enum, and set type, sql server does not support
mysql does not support nchar, nvarchar, ntext type
Mysql’s increment statement is AUTO_INCREMENT, and sql server is identity(1,1)
SQL server defaults to the default value of table creation statement ((0)), but in mysql, two parentheses are not allowed
mysql needs to specify the storage type for the table
The sql server identifier is [], [type] means that it is different from keywords, but mysql is `, which is the symbol on the left of button 1
sql server supports the getdate() method to obtain the current time and date. However, 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
mysql supports insert into table1 set t1 = '', t2 = '', but SQL server does not support writing like this
mysql supports insert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1 ,1), (1,1)
SQL server 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
Mysql needs to specify a storage engine type for each table when creating a table, and sql server only supports one storage engine
mysql does not support the datetime type whose default value is the current time (mssql is very Easy to do), in mysql, use the timestamp type
sql server 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;
mysql supports unsigned type Integer, then the maximum number storage can be twice as much as mssql that does not support unsigned type
mysql does not support the very convenient varchar (max) type in mssql. This type can be used in mssql For general data storage, you can also do blob data storage
Mysql creates a non-clustered index and only needs to specify it as the key when creating the table, for example: KEY displayorder (fid, displayorder) In mssql you must: create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers
(username asc,appid asc)
mysql text field type does not allow a default value
The total field length of a table in mysql No more than 65XXX.
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...
mysql There are 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.
The stored procedures of mysql only appear in the latest version, and the stability and performance may not be as good as mssql.
Under the same load pressure, mysql consumes less CPU and memory, while mssql is indeed very resource intensive.
The methods of connecting mysql and mssql in php are similar. You only need to replace the mysql of the function with mssql.
Mysql supports date, time, and year types, and mssql only supports date and time in 2008.
1. Data definition
Basic commands for database operations
Mysql:
create database name; Create database
use databasename; Select database
drop database name Delete the database directly without reminder-
CREATE TABLE --Create a database table
2.1 PRIMARY KEY constraint (primary key) difference analysis:
Mysql: 2.1.1 创建primary key CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (Id_P) //声明主健写在最后 ) SqlServer： CREATE TABLE Persons ( Id_P int NOT NULL PRIMARY KEY, //声明主健 紧跟列后 LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) 但是如果表存在，之后给表加主健时： Mysql 和SqlServer ALTER TABLE Persons ADD PRIMARY KEY (Id_P) 2.1.2撤销 PRIMARY KEY 约束 MySQL: ALTER TABLE Persons DROP PRIMARY KEY SQL Server ALTER TABLE Persons DROP CONSTRAINT pk_PersonID 2.1.3 创建外健约束 MySQL: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, Id_P int, PRIMARY KEY (O_Id), FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) //写在最后 )
SQL Server ： CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, Id_P int FOREIGN KEY REFERENCES Persons(Id_P) //顺序不同 ) 如果在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL： MySQL / SQL Server ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) 2.1.4 撤销外健约束 MySQL: ALTER TABLE Orders DROP FOREIGN KEY f k_PerOrders SQL Server ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
2.2 UNIQUE constraint (unique, unique) difference analysis
UNIQUE constraint uniquely identifies the database table per record.
UNIQUE and PRIMARY KEY constraints both provide uniqueness guarantees for columns or column sets.
PRIMARY KEY has automatically defined UNIQUE constraints.
Please note that each table can have multiple UNIQUE constraints, but each table can only have one PRIMARY KEY constraint.
2.2.1 Create UNIQUE constraints
MySQL:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P) //Write at the end
)
SQL Server
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE, //immediately after the column
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
2.2.2 Revoke UNIQUE constraint
MySQL:
ALTER TABLE Persons DROP INDEX uc_PersonID
SQL Server
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID
2.3 CHECK Constraints
CHECK constraints are used to limit the range of values.
If a CHECK constraint is defined on a single column, only specific values are allowed for that column.
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
2.3.1 创建 CHECK约束
下面的 SQL 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数。 My SQL: CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (Id_P>0) //写在最后 )
SQL Server CREATE TABLE Persons ( Id_P int NOT NULL CHECK (Id_P>0), //紧跟列后 LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) )
如果需要命名 CHECK 约束，以及为多个列定义 CHECK 约束，请使用下面的 SQL 语法：
MySQL / SQL Server: CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') //多个条件 )
如果在表已存在的情况下为 “Id_P” 列创建 CHECK 约束，请使用下面的 SQL：
MySQL / SQL Server: ALTER TABLE Persons ADD CHECK (Id_P>0)
2.3.2 撤销 CHECK约束
Sqlserver: ALTER TABLE Persons DROP CONSTRAINT chk_Person Mysql我没有找到怎么删除。
2.4 DEFAULT 约束（系统默认值）
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新纪录。
2.4.1 创建DEFAULT约束
下面的 SQL 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束： My SQL / SQL Server: CREATE TABLE Persons ( Id_P int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' //紧跟列后，默认值字符串Sandnes )
通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders ( Id_O int NOT NULL, OrderNo int NOT NULL, Id_P int, OrderDate date DEFAULT GETDATE() //紧跟列后，函数 )
如果在表已存在的情况下为 “City” 列创建 DEFAULT 约束，请使用下面的 SQL：
MySQL: ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' SQL Server: ALTER TABLE Persons ALTER COLUMN City SET DEFAULT 'SANDNES'
2.4 .2 撤消DEFAULT约束
MySQL: ALTER TABLE Persons ALTER City DROP DEFAULT SQL Server: ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT
2.5 索引区别
CREATE INDEX 语句
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name ON table_name (column_name) //“column_name” 规定需要索引的列。
在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
Mysql和SqlServer的创建索引都是一致的，但是在删除索引方面却有区别:
SqlServer: DROP INDEX table_name.index_name Mysql: ALTER TABLE table_name DROP INDEX index_name
2.6 主键自动增加的区别
mySql的主键自动增加是用auto_increment字段，sqlServer的自动增加则是identity字段.
Auto-increment 会在新纪录插入表中时生成一个唯一的数字。
我们通常希望在每次插入新纪录时，自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment 字段。
关于 MySQL 的语法 下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键： CREATE TABLE Persons ( P_Id int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。 默认地，AUTO_INCREMENT 的开始值是 1，每条新纪录递增 1。 要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法： ALTER TABLE Persons AUTO_INCREMENT=100
关于 SQL Server 的语法 下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键： CREATE TABLE Persons ( P_Id int PRIMARY KEY IDENTITY,或则是写成P_id int primary key identity (1,1), LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务。 默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。 要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)
2.7 MySQL支持enum,和set类型，SQL Server不支持
2.8 MySQL不支持nchar,nvarchar,ntext类型
二、数据操作
2.1 limit和top
SQL SERVER : select top 8 * from table1 MYSQL: select * from table1 limit 5或则是 limit 0,5; 注意，在MySQL中的limit不能放在子查询内，limit不同与top,它可以规定范围 limit a,b——范围a-b
2.2 ISNULL()函数
SqlServer: select * from test where isnull(no,0)=0; MySQL MySQL 可以使用 ISNULL() 函数。不过它的工作方式与微软的 ISNULL() 函数有点不同。 在 MySQL 中，我们可以使用 IFNULL() 函数，就像这样： mysql> select * from test where ifnull(no,0)=0; row in set (0.03 sec)
2.3 select查询
SELECT * FROM tablename
2.4 insert 插入
INSERT INTO table(col1,col2) values(value1,value2);
MySQL支持insert into table1 set t1 = ‘’, t2=‘’,但是MSSQL不支持这样写
2.5 update 修改
Update tablename set col=”value”;
2.6 delete 删除
Delete from tablename;
三、语法定义
3.1 注释符区别
SqlServer的注释符为 --和/**/
MySql的注释符为 --和/**/和#
3.2 识别符的区别
MS SQL识别符是[],[type]表示他区别于关键字，但是MySQL却是 `，也就是按键1左边的那个符号
3.3存储过程的区别（未经验证，从网上找的）
(1) mysql的存储过程中变量的定义去掉@;
(2) SQLServer存储过程的AS在MySql中需要用begin …end替换
(3) Mysql的Execute对应SqlServer的exec;
(注意：必须想下面这样调用)
Set @cnt=’select * from 表名’;
Prepare str from @cnt;
Execute str;
(4) MySql存储过程调用其他存储过程用call
Call 函数名（即SQLServer的存储过程名）（’参数1’,’参数2’,……）
(5) select @a=count() from VW_Action 在mySql中修改为：select count() from VW_Action into @a;
(6) MySQL视图的FROM子句不允许存在子查询，因此对于SQL Server中FROM子句带有子查询的视图，需要手工进行迁移。可通过消除FROM子句中的子查询，或将FROM子句中的子查询重构为一个新的视图来进行迁移。
(7) )MySql存储过程中没有return函数，在MySql中可以用循环和out参数代替
If EXISTS(SELECT * FROM T_Chance WHERE FCustID=CostomerID) return 0 改写为： （在参数中定义一个out变量：out temp varchar(100);） BEGIN Loop1:loop SELECT count(*) FROM T_Chance WHERE FCustID=CostomerID int @cnt If @cnt>0 then begin set temp=0; leave loop1; end; end if end loop loop1;
(8) mysql的uuid()对应sql的GUID();
(9) MySql的out对应SQLServer的output,且mysql 的out要放在变量的前面，SQLServer的output放在变量后面：
MySql out,in,inout的区别——
MySQL 存储过程 “in” 参数：跟 C 语言的函数参数的值传递类似， MySQL 存储过程内部可能会修改此参数，但对 in 类型参数的修改，对调用者（caller）来说是不可见的（not visible）。
MySQL 存储过程 “out” 参数：从存储过程内部传值给调用者。在存储过程内部，该参数初始值为 null，无论调用者是否给存储过程参数设置值。
MySQL 存储过程 inout 参数跟 out 类似，都可以从存储过程内部传值给调用者。不同的是：调用者还可以通过 inout 参数传递值给存储过程。
3.4字符串连接
SQLServer: Temp=’select * from ’+’tablename’+…+…
MySql:Temp=concat(’select * from’, ’tablecname’,…,…)
四、函数和数据类型的区别
4.1 Date 函数
MySQL Date 函数
SQL Server Date 函数
4.2 Date 数据类型
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值： DATE - 格式 YYYY-MM-DD DATETIME - 格式: YYYY-MM-DD HH:MM:SS TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS YEAR - 格式 YYYY 或 YY SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值： DATE - 格式 YYYY-MM-DD DATETIME - 格式: YYYY-MM-DD HH:MM:SS SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS TIMESTAMP - 格式: 唯一的数字
五、性能比较
（1）一个很表面的区别就是MySQL的安装特别简单，而且文件大小才110M(非安装版)，相比微软这个庞然大物，安装进度来说简直就是…
（2）MySQL的管理工具有几个比较好的，MySQL_front,和官方那个套件，不过都没有SSMS的使用方便，这是MySQL很大的一个缺点。
（3）MySQL的存储过程只是出现在最新的版本中，稳定性和性能可能不如MS SQL。
（4）同样的负载压力，MySQL要消耗更少的CPU和内存，MS SQL的确是很耗资源。
1、把主键定义为自动增长标识符类型
MySql
在mysql中，如果把表的主键设为auto_increment类型，数据库就会自动为主键赋值。例如：
create table customers(id int auto_increment primary key not null, name varchar(15));
I have recently been working on converting mssql 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.
mysql supports enum, and set types, sql server does not support
mysql does not support nchar, nvarchar, ntext types
The increment statement of mysql is AUTO_INCREMENT, while mssql is identity (1, 1)
msms The default value of the table creation statement is ((0)), and two brackets are not allowed in mysql
Mysql needs to specify the storage type for the table
The mssql identifier is [], [type] It means that it is different from keywords, but mysql is `, which is the symbol on the left of button 1
mssql 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 date is cur_date(), the current complete time is the now() function
mssql does not support the replace into statement, but in the latest sql20008, the merge syntax is also supported
mysql supports insert into table1 set t1 = '', t2 = ' ' , but mssql does not support writing like this
mysql supports insert into tabl1 values (1,1), (1,1), (1,1), (1,1), (1,1), (1, 1), (1,1)
It is a pity that mssql does not support the limit statement. You can only use top to replace limit 0,N, and the row_number() over() function to replace limit N,M
when creating mysql When creating a table, you need to specify a storage engine type for each table, and mssql only supports one storage engine
Mysql does not support the datetime type whose default value is the current time (mssql is easy to do). In mysql, the timestamp type is used
In mssql, check whether this table exists 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;
mysql supports unsigned integers, so it can store twice the maximum number than mssql that does not support unsigned integers
mysql The very convenient varchar(max) type in mssql is not supported. This type can be used for both general data storage and blob data storage in mssql.
To create a non-clustered index in mysql, you only need to specify it when creating the table. Key is enough, for example: KEY displayorder (fid, displayorder) In mssql it is necessary: create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers
(username asc,appid asc)
Mysql text field type does not allow default values
19The total field length of a table in mysql 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...
21There are several management tools for mysql The better ones are 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 mssql.
Under the same load pressure, mysql consumes less CPU and memory, while mssql is indeed very resource intensive.
24php has similar methods of connecting mysql and mssql. You only need to replace the mysql of the function with mssql.
25mysql supports date, time, and year types, and mssql only supports date and time in 2008.
MySQL's numeric data types can be roughly divided into two categories, one is integer, and the other is floating point number or decimal.
Many different subtypes are available for each of these categories, each subtype supports different sizes of data, and MySQL allows us to specify whether values in numeric fields are positive or negative or padded with zeros.
The following table lists various numeric types, their allowed ranges and the memory space they occupy.
| Type | Size | Range (signed) | Range (unsigned) | Purpose |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | Small integer value |
| SMALLINT | 2 bytes | (-32 768, 32 767) | (0, 65 535) | Large integer value |
| MEDIUMINT | 3 Bytes | (-8 388 608, 8 388 607) | (0, 16 777 215) | Large integer value |
| INT/INTEGER | 4 bytes | (-2 147 483 648, 2 147 483 647) | (0, 4 294 967 295) | Big integer value |
| BIGINT | 8 bytes | (-9 233 372 036 854 775 808, 9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | Maximum integer value |
| FLOAT | 4 bytes | (-3.402 823 466 E 38, 1.175 494 351 E-38) | , (1.175 494 351 E-38, 3.402 823 466 351 E 38) | Single precision |
| Size | Range (signed) | Range (unsigned) | Use | |
|---|---|---|---|---|
| 8 Bytes | (1.797 693 134 862 315 7 E 308, 2.225 073 858 507 201 4 E-308), 0 | (2.225 073 858 507 201 4 E -308, 1.797 693 134 862 315 7 E 308), 0 | Double |
| Type | Size | Use |
|---|---|---|
| CHAR | 0-255 bytes | Fixed length string |
| VARCHAR | 0-255 bytes | Variable length string |
| TINYBLOB | 0-255 bytes | Binary string not exceeding 255 characters |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65 535 Bytes | Long text data in binary form |
| TEXT | 0-65 535 bytes | Long text data |
| MEDIUMBLOB | 0-16 777 215 bytes | Medium-length text data in binary form |
| MEDIUMTEXT | 0-16 777 215 bytes | Medium length text data |
| LOGNGBLOB | 0-4 294 967 295 bytes | Extremely large text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | Extremely large text data |
CHAR and VARCHAR types
TEXT and BLOB types
For cases where the field length requirement exceeds 255, MySQL provides two types: TEXT and BLOB. They all have different subtypes based on the size of the stored data. These large data are used to store text blocks or binary data types such as images and sound files.
There are differences in classification and comparison between TEXT and BLOB types. The BLOB type is case-sensitive, while TEXT is not case-sensitive. Size modifiers are not used on various BLOB and TEXT subtypes. Values larger than the maximum range supported by the specified type will be automatically truncated.
Date and time types
When dealing with date and time type values, MySQL comes with 5 different data types to choose from. They can be divided into simple date and time types, and mixed date and time types. Depending on the required precision, subtypes can be used within each subtype, and MySQL has built-in functionality to convert diverse input formats into a standard format.
Type size
(bytes) Range format usage
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD Date value
TIME 3 '-838:59:59 '/'838:59:59' HH:MM:SS Time value or duration
YEAR 1 1901/2155 YYYY Year value
DATETIME 8 1000-01-01 00:00:00/9999-12- 31 23:59:59 YYYY-MM-DD HH:MM:SS Mixed date and time values
TIMESTAMP 8 1970-01-01 00:00:00/Sometime in 2037 YYYYMMDD HHMMSS Mixed date and time values, time stamp
DATE, TIME and TEAR types
MySQL uses the DATE and TEAR types to store simple date values, and the TIME type to store time values. These types can be described as strings or sequences of integers without delimiters. If described as strings, values of type DATE should be separated by hyphens as delimiters, and values of type TIME should be separated by colons as delimiters.
It should be noted that a TIME type value without a colon separator will be understood by MySQL as a duration, not a timestamp.
MySQL also interprets to the maximum extent the value of two digits in the year of a date, or two digits entered for the TEAR type in an SQL statement. Because all TEAR type values must be stored with 4 numbers. MySQL attempts to convert a 2-digit year to a 4-digit value. Converts values in the range 00-69 to the range 2000-2069. Converts values in the range 70-99 to 1970-1979. If the value automatically converted by MySQL does not meet our needs, enter a 4-digit year.
DATEYIME and TIMESTAMP types
In addition to date and time data types, MySQL also supports two mixed types, DATEYIME and TIMESTAMP. They can store date and time as a single value. Both types are commonly used to automatically store timestamps containing the current date and time, and can work well in applications that need to perform a large number of database transactions and need to establish an audit trail for debugging and review purposes.
If we do not explicitly assign a value to a field of TIMESTAMP type, or it is assigned a null value. MySQL automatically populates it with the system's current date and time.
Composite types
MySQL also supports two composite data types, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be treated as different data types. An ENUM type allows only one value to be obtained from a collection; a SET type allows any number of values to be obtained from a collection.
ENUM type
The ENUM type only allows one value to be obtained in the collection, which is somewhat similar to a single option. Easier to understand when dealing with mutually exclusive data, such as human gender. ENUM type fields can take a value from a collection or use a null value, otherwise input will cause MySQL to insert an empty string into the field. In addition, if the case of the inserted value does not match the case of the values in the collection, MySQL will automatically use the case of the inserted value to convert it to a value consistent with the case of the collection.
The ENUM type can be stored as a number within the system, and is indexed starting from 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error information. This error value is represented by index 0 or an empty string.
MySQL considers the values appearing in the ENUM type collection to be legal input, and any other input will fail. This shows that the location of the erroneous record can be easily found by searching for rows that contain an empty string or a corresponding numeric index of 0.
SET type
The SET type is similar to the ENUM type but not the same. The SET type can take any number of values from a predefined collection. And like the ENUM type, any attempt to insert a non-predefined value in a SET type field will cause MySQL to insert an empty string. If you insert a record that contains both legal and illegal elements, MySQL will retain the legal elements and remove the illegal elements.
A SET type can contain up to 64 elements. In a SET element the value is stored as a discrete sequence of "bits" that represent its corresponding element. Bits are a simple and efficient way to create ordered collections of elements. And it also removes duplicate elements, so it is impossible to contain two identical elements in a SET type.
If you want to find illegal records from SET type fields, just look for rows containing empty strings or binary values of 0.
For more programming-related knowledge, please visit: Programming Learning! !
The above is the detailed content of What is the difference between mysql and sql server. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
