Home > Article > Database > What is the difference between mysql and sql server

What is the difference between mysql and sql server

青灯夜游Original: 2020-12-16 12:10:026583browse

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)

Difference 1

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.

Difference 2

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 函数

NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间、
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

SQL Server Date 函数

GETDATE() 返回当前日期和时间
DATEPART() 返回日期/时间的单独部分
DATEADD() 在日期中添加或减去指定的时间间隔
DATEDIFF() 返回两个日期之间的时间
CONVERT() 用不同的格式显示日期/时间

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.

Integer:

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

##TypeDOUBLE

Floating point value
DECIMAL For DECIMAL(M,D), if M>D, it is M 2 otherwise it is D 2 Depends on the value of M and D Depends on the value of M and D Decimal value

INT type
The five main integer types supported in MySQL are TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. These types are largely the same, only the size of the values they store differ.
MySQL extends the SQL standard in the form of an optional display width indicator so that when a value is retrieved from the database, the value can be lengthened to a specified length. For example, specifying that a field is of type INT(6) ensures that values containing fewer than six digits are automatically padded with spaces when retrieved from the database. Note that using a width indicator does not affect the size of the field or the range of values it can store.
In case we need to store a number in a field that exceeds the allowed range, MySQL will truncate it according to the end of the allowed range closest to it before storing it. Another special thing is that MySQL will automatically change the illegal value to 0 before inserting it into the table.
The UNSIGNED modifier specifies that the field only stores positive values. Because there is no need to save the positive and negative signs of the numbers, one "bit" of space can be saved during storage. This increases the range of values that this field can store.
The ZEROFILL modifier specifies that 0 (not a space) can be used to complement the output value. Use this modifier to prevent the MySQL database from storing negative values.
　
　The three floating point types supported by MySQL are FLOAT, DOUBLE and DECIMAL types. The FLOAT numeric type is used to represent single-precision floating-point values, and the DOUBLE numeric type is used to represent double-precision floating-point values.
Like integers, these types also take additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT(7,3) specifies that the displayed value will not exceed 7 digits, with 3 digits after the decimal point.
For a value with more digits after the decimal point than the allowed range, MySQL will automatically round it to the nearest value and then insert it.
The DECIMAL data type is used in calculations with very high accuracy requirements. This type allows you to specify the precision and counting method of the value as selection parameters. The precision here refers to the total number of significant digits saved for the value, while the counting method indicates the number of digits after the decimal point. For example, the statement DECIMAL(7,3) specifies that the stored value will not exceed 7 digits and will not exceed 3 digits after the decimal point.
Ignoring the precision and counting method modifiers of the DECIMAL data type will cause the MySQL database to set the precision of all fields identified as this data type to 10 and the counting method to 0.
The UNSIGNED and ZEROFILL modifiers can also be used with the FLOAT, DOUBLE and DECIMAL data types. And the effect is the same as the INT data type.
String types
MySQL provides 8 basic string types, which can store anything from a simple character to a huge text block or binary string data.

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

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. This size modifier ranges from
0-255. Values larger than the specified length will be truncated, while values smaller than the specified length will be padded with spaces.
The CHAR type can use the BINARY modifier. When used in comparison operations, this modifier causes CHAR
to participate in the operation in binary form, rather than in the traditional case-sensitive manner.
A variant of the CHAR type is the VARCHAR type. It is a variable-length string type and must also have an indicator in the range 0-255. The difference between CHAR and VARCHGAR is the way the MuSQL database handles this indicator: CHAR treats the size as the size of the value, and pads it with spaces if the length is not sufficient.
The VARCHAR type treats it as the maximum value and only uses the length actually required to store the string (adding an extra byte to store the length of the string itself) to store the value. So shorter than the indicator length.
VARCHAR types are not padded with spaces, but values longer than the indicator are still truncated.
The VARCHAR type can dynamically change the length of the stored value based on the actual content, so using the VARCHAR type can greatly save disk space and improve storage efficiency when you are not sure how many characters a field requires.
The VARCHAR type is identical to the CHAR type when using the BINARY modifier.

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!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：How to add pictures to mysqlNext article：How to add pictures to mysql

See more

What is the difference between mysql and sql server

Difference 1

Difference 2

Related articles