Shulou(Shulou.com)06/01 Report--

This article is to share with you about the difference between mysql and sql server. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

Differences: 1, mysql supports enum and set types, sql server does not support; 2, the increment statement of mysql is "AUTO_INCREMENT", while sql server is "identity (1jue 1)"; 3, sql server does not support limit statement.

Difference one

Mysql supports enum, and set types, but sql server does not

Mysql does not support nchar,nvarchar,ntext type

The incrementing statement of mysql is AUTO_INCREMENT, while sql server is identity.

By default, the default value of sql server everywhere table creation statement is ((0)), but two parentheses are not allowed in mysql.

Mysql needs to specify the storage type for the table

The sql server identifier is [], and [type] indicates that it is different from the keyword, but mysql is `, the symbol to the left of button 1.

Sql server supports the getdate () method to get 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 full 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)

It is a pity that sql server does not support limit statements. It is a pity that top can only replace limt 0meme NMagi number () over () function instead of limit NMagi M

Mysql specifies one storage engine type for each table when creating tables, while sql server supports only one storage engine

Mysql does not support the datetime type that defaults to the current time (mssql is easy to do). In mysql, the timestamp type is used.

To check whether the table is deleted in sql server, you need to do this:

If exists (select * from dbo.sysobjects where id = object_id (newpm') and OBJECTPROPERTY (id, Noble IsUserTable') = 1)

But in mysql, only DROP TABLE IF EXISTS cdb_forums is needed.

Mysql supports unsigned integers, so there is twice as much maximum storage as unsigned mssql.

Mysql does not support the very convenient varchar (max) type in mssql. This type can be used for both general data storage and blob data storage in mssql.

To create a nonclustered index by mysql, you only need to specify it as key when creating the table. For example, KEY displayorder (fid,displayorder) must be create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers in mssql.

(username asc,appid asc)

Default values are not allowed for mysql text field types

The total field length of a table in mysql does not exceed 65XXX.

A superficial difference is that mysql installation is very simple, and the file size is only 110m (non-installation version), compared with the Microsoft behemoth, the installation progress is simply …

Mysql has several good management tools, mysql_front, and the official suite, but they are not as easy to use as SSMS, which is a big disadvantage of mysql.

Mysql stored procedures only appear in the latest version, and their stability and performance may not be as stable as mssql.

With the same load pressure, mysql consumes less CPU and memory, and mssql really consumes a lot of resources.

Php connects mysql and mssql in the same way, just replace the mysql of the function with mssql.

Mysql supports date,time,year types, while mssql does not support date and time until 2008.

Difference two

I. data definition

Basic commands for database operation

Mysql:

Create database name; creates a database

Use databasename; Select Database

Drop database name deletes the database directly without reminding-

CREATE TABLE-create a database table

2.1Discriminant resolution of PRIMARY KEY constraints (primary keys):

Mysql: 2.1.1 create primary key CREATE TABLE Persons (Id_P int NOT NULL, LastName varchar (255) NOT NULL, FirstName varchar (255), Address varchar (255) City varchar, PRIMARY KEY (Id_P) / / the declaration key is written at the end) SqlServer: CREATE TABLE Persons (Id_P int NOT NULL PRIMARY KEY / / declare the main key immediately after the column LastName varchar, FirstName varchar, Address varchar, City varchar), but if the table exists Then add the key to the table: Mysql and SqlServer ALTER TABLE Persons ADD PRIMARY KEY (Id_P) 2.1.2 undo the PRIMARY KEY constraint MySQL: ALTER TABLE Persons DROP PRIMARY KEY SQL Server ALTER TABLE Persons DROP CONSTRAINT pk_ PersonID2.1.3 creates external key constraint 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) / / at the end) 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) / / different order) if you create a FOREIGN KEY constraint for the "Id_P" column if the "Orders" table already exists. Use the following SQL: MySQL / SQL Server ALTER TABLE Orders ADD FOREIGN KEY (Id_P) REFERENCES Persons (Id_P) 2.1.4 to undo the external key constraint MySQL: ALTER TABLE Orders DROP FOREIGN KEY f k_PerOrders SQL Server ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

2.2 distinguishing resolution of UNIQUE constraints (unique, unique)

The UNIQUE constraint uniquely identifies each record in the database table.

Both UNIQUE and PRIMARY KEY constraints provide a guarantee of uniqueness for columns or sets of columns.

PRIMARY KEY has automatically defined UNIQUE constraints.

Note that each table can have multiple UNIQUE constraints, but each table can have only one PRIMARY KEY constraint.

2.2.1 create UNIQUE constraint

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) / / written 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 Undo UNIQUE constraint

MySQL:

ALTER TABLE Persons DROP INDEX uc_PersonID

SQL Server

ALTER TABLE Persons DROP CONSTRAINT uc_PersonID

2.3 CHECK constraint

The CHECK constraint is used to limit the range of values in a column.

If CHECK constraints are defined on a single column, only specific values are allowed for that column.

If a CHECK constraint is defined on a table, the constraint restricts values in specific columns.

2.3.1 create CHECK constraint

The following SQL creates a CHECK constraint for the "Id_P" column when the "Persons" table is created. The CHECK constraint states that the "Id_P" column must contain only integers greater than 0. My SQL:CREATE TABLE Persons (Id_P int NOT NULL,LastName varchar) NOT NULL,FirstName varchar, Address varchar, City varchar, CHECK (Id_P > 0) / / write last) SQL Server CREATE TABLE Persons (Id_P int NOT NULL CHECK (Id_P > 0), / / immediately following the column LastName varchar (255NOT NULL,FirstName varchar), Address varchar (255), City varchar (255)

If you need to name CHECK constraints and define CHECK constraints for multiple columns, use the following SQL syntax:

MySQL / SQL Server:CREATE TABLE Persons (Id_P int NOT NULL,LastName varchar (255) NOT NULL,FirstName varchar, Address varchar (255), City varchar (255), CONSTRAINT chk_Person CHECK (Id_P > 0 AND City='Sandnes') / / multiple conditions)

If you create a CHECK constraint for the "Id_P" column when the table already exists, use the following SQL:

MySQL / SQL Server:ALTER TABLE Persons ADD CHECK (Id_P > 0)

2.3.2 undo CHECK constraint

Sqlserver:ALTER TABLE Persons DROP CONSTRAINT chk_PersonMysql, I can't find how to delete it.

2.4 DEFAULT constraints (system default)

The DEFAULT constraint is used to insert default values into the column.

If no other values are specified, default values are added to all new records.

2.4.1 create DEFAULT constraint

The following SQL creates a DEFAULT constraint for the "City" column when the "Persons" table is created: 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' / / immediately after the column, the default string Sandnes)

DEFAULT constraints can also be used to insert system values by using functions such as GETDATE ():

CREATE TABLE Orders (Id_O int NOT NULL,OrderNo int NOT NULL,Id_P int,OrderDate date DEFAULT GETDATE () / / immediately after the column, function)

If you create a DEFAULT constraint for the "City" column when the table already exists, use the following SQL:

MySQL:ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES'SQL Server:ALTER TABLE Persons ALTER COLUMN City SET DEFAULT' SANDNES'

2. 2 undo DEFAULT constraint

MySQL:ALTER TABLE Persons ALTER City DROP DEFAULTSQL Server:ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

2.5 Index differences

CREATE INDEX statement

The CREATE INDEX statement is used to create an index in a table.

Indexes enable database applications to find data faster without reading the entire table.

Create a simple index on the table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name) / / "column_name" specifies the columns to be indexed.

Create a unique index on the table. A unique index means that two rows cannot have the same index value.

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Both Mysql and SqlServer create the same index, but there are differences in deleting the index:

SqlServer: DROP INDEX table_name.index_nameMysql: ALTER TABLE table_name DROP INDEX index_name

2.6 differences in automatic increment of primary keys

The primary key auto-increment of mySql is the auto_increment field, and the sqlServer auto-increment is the identity field.

Auto-increment generates a unique number when a new record is inserted into the table.

We usually want to automatically create the value of the primary key field each time a new record is inserted.

We can create an auto-increment field in the table.

About the syntax of MySQL the following SQL statement defines the "P_Id" column in the "Persons" table as the auto-increment primary key: CREATE TABLE Persons (P_Id int NOT NULL AUTO_INCREMENT,LastName varchar (255) NOT NULL,FirstName varchar, Address varchar (255), City varchar (255), PRIMARY KEY (P_Id)) MySQL uses the AUTO_INCREMENT keyword to perform auto-increment tasks. By default, the starting value of AUTO_INCREMENT is 1, and each new record is incremented by 1. To start the AUTO_INCREMENT sequence with a different value Use the following SQL syntax: ALTER TABLE Persons AUTO_INCREMENT=100 syntax for SQL Server the following SQL statements define the "P_Id" column in the "Persons" table as the auto-increment primary key: CREATE TABLE Persons (P_Id int PRIMARY KEY IDENTITY, or written as P_id int primary key identity (1), LastName varchar (255) NOT NULL,FirstName varchar (255), Address varchar (255), City varchar (255) MS SQL uses the IDENTITY keyword to perform auto-increment tasks. By default, the starting value of IDENTITY is 1, and each new record is incremented by 1. To specify that the "P_Id" column starts with 20 and increases by 10, change identity to IDENTITY (20, 10).

2.7 MySQL supports enum, and set types, but not SQL Server.

2.8The nchar,nvarchar,ntext type is not supported by MySQL

II. Data operation

2.1 limit and top

SQL SERVER: select top 8 * from table1 MYSQL: select * from table1 limit 5 or limit 0 from table1 limit 5; note that limit in MySQL cannot be placed in a subquery. Limit, unlike top, can specify the range limit a-scope b-b

2.2 ISNULL () function

SqlServer:select * from test where isnull (no,0) = 0 * MySQL MySQL can use the ISNULL () function. But it works a little different from Microsoft's ISNULL () function. In MySQL, we can use the IFNULL () function, like this: mysql > select * from test where ifnull (no,0) = 0bot row in set (0.03 sec)

2.3 select query

SELECT * FROM tablename

2.4 insert insertion

INSERT INTO table (col1,col2) values (value1,value2)

MySQL supports insert into table1 set T1 ='', T2 custom writing, but MSSQL does not support writing like this

2.5 update modification

Update tablename set col= "value"

2.6 delete deletion

Delete from tablename

III. Grammatical definition

3.1 Annotation distinction

The commentators of SqlServer are-- and / * /

The MySql commentators are-- and / * / and #

3.2 differences in identifiers

The MS SQL identifier is [], and [type] indicates that it is different from the keyword, but MySQL is `, the symbol to the left of button 1.

3.3 differences in stored procedures (unverified, found online)

(1) remove @ from the definition of variables in the stored procedure of mysql

(2) the AS of SQLServer stored procedure needs to use begin in MySql. End replacement

(3) Execute of Mysql corresponds to exec of SqlServer

(note: it must be called as follows)

Set @ cnt='select * from table name'

Prepare str from @ cnt

Execute str

(4) MySql stored procedure calls other stored procedures with call

The name of the Call function (that is, the stored procedure name of SQLServer) ('parameter 1', 'parameter 1', parameter 2, etc. )

(5) select @ a=count () from VW_Action is modified to: select count () from VW_Action into @ an in mySql

(6) subqueries are not allowed in the FROM clause of the MySQL view, so views with subqueries in the FROM clause in SQL Server need to be migrated manually. You can migrate by eliminating the subquery in the FROM clause or refactoring the subquery in the FROM clause into a new view.

(7) there is no return function in MySql stored procedure, so loop and out parameters can be used instead in MySql.

If EXISTS (SELECT * FROM T_Chance WHERE FCustID=CostomerID) return 0 is rewritten as: (define an out variable in the parameter: out temp varchar;) 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) uuid () of mysql corresponds to GUID () of sql.

(9) the out of MySql corresponds to the output of SQLServer, and the out of mysql should be placed in front of the variable, and the output of SQLServer should be placed after the variable:

The difference between MySql out,in,inout

MySQL stored procedure "in" parameter: similar to the value passing of function parameters in C language, this parameter may be modified within the MySQL stored procedure, but changes to in type parameters are not visible to the caller.

The MySQL stored procedure "out" parameter: passes a value from within the stored procedure to the caller. Inside the stored procedure, the initial value of this parameter is null, regardless of whether the caller sets the value for the stored procedure parameter or not.

The MySQL stored procedure inout parameter, like out, can be passed to the caller from within the stored procedure. The difference is that the caller can also pass a value to the stored procedure through the inout parameter.

3.4 string concatenation

SQLServer: Temp='select * from'+ 'tablename'+... +...

MySql:Temp=concat ('select * from',' tablecname',... ,...)

Fourth, the difference between function and data type

4.1 Date function

MySQL Date function

NOW () returns the current date and time

CURDATE () returns the current date

CURTIME () returns the current time,

DATE () extracts the date portion of a date or date / time expression

EXTRACT () returns a separate part of the date / time press

DATE_ADD () adds a specified time interval to the date

DATE_SUB () subtracts the specified time interval from the date

DATEDIFF () returns the number of days between two dates

DATE_FORMAT () displays the date / time in different formats

SQL Server Date function

GETDATE () returns the current date and time

DATEPART () returns a separate part of the date / time

DATEADD () adds or subtracts the specified time interval from the date

DATEDIFF () returns the time between two dates

CONVERT () displays the date / time in different formats

4.2 Date data type

MySQL uses the following data types to store date or date / time values in the database: DATE-format YYYY-MM-DD DATETIME-format: YYYY-MM-DD HH:MM:SS TIMESTAMP-format: YYYY-MM-DD HH:MM:SS YEAR-format YYYY or YYSQL Server uses the following data types to store date or date / time values in the database: DATE-format YYYY-MM-DD DATETIME-format: YYYY- MM-DD HH:MM:SS SMALLDATETIME-format: YYYY-MM-DD HH:MM:SS TIMESTAMP-format: unique number

Fifth, performance comparison

(1) A 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 of Microsoft, the installation progress is simply …

(2) there are several good management tools for MySQL, MySQL_front, and the official suite, but they are not as easy to use as SSMS, which is a big disadvantage of MySQL.

(3) MySQL stored procedures only appear in the latest version, and their stability and performance may not be as good as MS SQL.

(4) under the same load pressure, MySQL consumes less CPU and memory, and MS SQL really consumes a lot of resources.

1. Define the primary key as an auto-growing identifier type

MySql

In mysql, if the primary key of a table is set to type auto_increment, the database automatically assigns a value to the primary key. For example:

Create table customers (id int auto_increment primary key not null, name varchar (15))

Recently, I have been working on the conversion from mssql to mysql. I have summed up some experience and shared it with you.

At the same time, these will be constantly updated. I also hope that you can add.

Mysql supports enum, and set types, but sql server does not

Mysql does not support nchar,nvarchar,ntext type

The incrementing statement of mysql is AUTO_INCREMENT, while mssql is identity.

By default, the default value of msms everywhere table creation statement is ((0)), but two parentheses are not allowed in mysql.

Mysql needs to specify the storage type for the table

The mssql identifier is [], and [type] indicates that it is different from the keyword, but mysql is `, the symbol to the left of button 1.

Mssql supports the getdate () method to get 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 full time is the now () function.

Mssql does not support replace into statements, but in the latest sql20008, 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)

It is a pity that mssql does not support limit statements. It is a pity that top can only replace limt 0meme NMagi number () over () function instead of limit NMagi M

Mysql specifies one storage engine type for each table when creating tables, while mssql supports only one storage engine

Mysql does not support the datetime type that defaults to the current time (mssql is easy to do). In mysql, the timestamp type is used.

To check whether the table is deleted in mssql, you need to do this:

If exists (select * from dbo.sysobjects where id = object_id (newpm') and OBJECTPROPERTY (id, Noble IsUserTable') = 1)

But in mysql, only DROP TABLE IF EXISTS cdb_forums is needed.

Mysql supports unsigned integers, so there is twice as much maximum storage as unsigned mssql.

Mysql does not support the very convenient varchar (max) type in mssql. This type can be used for both general data storage and blob data storage in mssql.

To create a nonclustered index by mysql, you only need to specify it as key when creating the table. For example, KEY displayorder (fid,displayorder) must be create unique nonclustered index index_uc_protectedmembers_username_appid on dbo.uc_protectedmembers in mssql.

(username asc,appid asc)

Default values are not allowed for mysql text field types

The total field length of a table in 19mysql does not exceed 65XXX.

20 A 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 of Microsoft, the installation progress is simply …

21mysql has several good management tools, mysql_front, and the official suite, but they are not as easy to use as SSMS, which is a big disadvantage of mysql.

22mysql stored procedures only appear in the latest version, and their stability and performance may not be as stable as mssql.

With the same load pressure, mysql consumes less CPU and memory, and mssql really consumes a lot of resources.

24php connects mysql and mssql in the same way, just replace the mysql of the function with mssql.

25mysql supports date,time,year types, while mssql does not support date and time until 2008.

The numeric data types of MySQL can be roughly divided into two categories, one is an integer and the other is a floating point 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 the values in the numeric field are positive or negative or filled with zeros.

The following table lists the various numeric types as well as their allowable range and memory footprint.

Integer:

Type size range (signed) range (unsigned) usage TINYINT1 byte (- 128127) (0255) small integer value SMALLINT2 byte (- 32 768 minus 32 767) (0meme 65 535) large integer value MEDIUMINT3 byte (- 8 388 608) (0mei 16 777 215) large integer value INT/INTEGER4 byte (- 2 147 483 648 min 2 147 483 647) (0min 4 294 967 295) large integer value BIGINT8 byte (- 9 233 372 036 854 775 808) 9 223 372 036 854 775 807) (0Magic 18 446 744 073 709 551 615) maximal integer value FLOAT4 bytes (- 3.402 823 466 Ehammer 38 Piaget 1.175 494 351 Emur38), (1.175 494,351 Eluto 38 3.402 823 466 351 Ecolors 38) single precision

Floating point value

Type size range (signed) range (unsigned) use DOUBLE8 bytes (1.797 693 134 862 315 7 Ecolors 308), 0 (2.225 073 858 507 201 4 Elam 308), 0 (2.225 073 858 507 201 4 Elam 308), 0 double precision

Floating point value

DECIMAL for DECIMAL (M < D), if M > D, it is M > D, otherwise, it depends on M and D. the values of M and D depend on the decimal values of M and D.

INT Typ

The five main integer types supported in MySQL are TINYINT,SMALLINT,MEDIUMINT,INT and BIGINT. These types are largely the same, except for the size of the values they store.

MySQL extends the SQL standard in the form of an optional display width indicator so that when a value is retrieved from the database, it can be lengthened to a specified length. For example, specifying a field of type INT (6) ensures that values with fewer than six numbers are automatically filled with spaces when retrieved from the database. It is important to note that using a width indicator does not affect the size of the field and the range of values it can store.

In case we need to store a number for a field that is out of the permitted range, MySQL truncates it according to the end closest to it before storing it. Another special feature is that MySQL is automatically changed to 0 before irregular values are inserted into the table.

The UNSIGNED modifier states that only positive values are saved in the field. Because there is no need to save the positive and negative symbols of numbers, you can save a "bit" space when storing. This increases the range of values that this field can store.

The ZEROFILL modifier specifies the value that 0 (not a space) can be used to really complement the output. 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. The FLOAT numeric type is used to represent single-precision floating-point values, while the DOUBLE numeric type is used to represent double-precision floating-point values.

Like integers, these types have additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT (7 and 3) specifies that the value displayed should not exceed 7 digits, followed by 3 digits after the decimal point.

If the number of digits after the decimal point exceeds the allowable range, MySQL automatically rounds it to the value closest to it, and then inserts it.

The DECIMAL data type is used in very precision-demanding calculations, and this type allows you to specify the precision of the numeric value and the counting method as a selection parameter. Precision here refers to the total number of valid digits saved for this value, while the counting method represents the number of digits after the decimal point. For example, the statement DECIMAL (7 and 3) specifies that the stored value should not exceed 7 digits and no more than 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 calculation method to 0.

The UNSIGNED and ZEROFILL modifiers can also be used by FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

String type

MySQL provides eight basic string types that can be stored from simple one character to large blocks of text or binary string data.

Type size use CHAR0-255byte fixed length string VARCHAR0-255byte variable length string TINYBLOB0-255byte binary string TINYTEXT0-255byte short text string BLOB0-65,535byte long text data TEXT0-65535byte long text data MEDIUMBLOB0-1677215byte medium length text data MEDIUMTEXT0-1677215byte medium length text data LOGNGBLOB0-4 294 967 295 byte maximum text data LONGTEXT0-4 294 967 295 byte maximum text data in binary form

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. The range of this size modifier is from

0-255. Values larger than the specified length will be truncated, while values smaller than the specified length will be filled with spaces.

CHAR types can use the BINARY modifier. When used for comparison operations, this modifier causes CHAR

Participate in operations in a binary way, rather than in the traditional case-sensitive way.

One 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 of 0-255. CHAR differs from VARCHGAR in the way the MuSQL database handles this indicator: CHAR treats this size as the size of the value, filling it with spaces if it is not long enough.

The VARCHAR type treats it as the maximum and uses only 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 length of the indicator.

The VARCHAR type will not be filled with blanks, but values longer than the indicator will still be truncated.

The VARCHAR type can dynamically change the length of the stored value according to 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 the field needs.

The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier.

TEXT and BLOB types

For cases where more than 255fields are required, MySQL provides two types: TEXT and BLOB. Depending on the size of the stored data, they all have different subtypes. 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. BLOB types are case sensitive, while TEXT is not case sensitive. Size modifiers are not used for various BLOB and TEXT subtypes. Values larger than the maximum range supported by the specified type are automatically truncated.

Date and time type

When dealing with values of date and time types, MySQL comes with five different data types to choose from. They can be divided into simple date and time types, and mixed date and time types. According to the required precision, subtypes can be used in each subtype, and MySQL has built-in features to turn a variety of input formats into a standard format.

Type size

(byte) range format usage

DATE 3 1000-01-01 YYYY-MM-DD date value

TIME 3'- 838 59 59 HH:MM:SS time value or duration

YEAR 1 1901x2155 YYYY year value

DATETIME 8 1000-01-01 00 YYYY-MM-DD HH:MM:SS mixed date and time values

TIMESTAMP 8 1970-01-01 00 YYYYMMDD HHMMSS mixed date and time values, time stamp

DATE, TIME, and TEAR types

MySQL uses DATE and TEAR types to store simple date values, and TIME types to store time values. These types can be described as strings or sequences of integers without delimiters. If described as a string, values of type DATE should be separated by hyphens, while values of type TIME should be separated by colons.

It is important to note that a TIME type value without a colon separator will be understood by MySQL as a duration rather than a timestamp.

MySQL also maximizes the translation of the values of two digits in the year of the date, or the two digits entered for the TEAR type in the SQL statement. Because values of all TEAR types must be stored in four digits. MySQL attempts to convert a 2-digit year to a 4-digit value. Convert values in the range 00-69 to the range 2000-2069. Convert values in the range 70-99 to 1970-1979. If the automatically converted value of MySQL does not meet our needs, please enter a year represented by 4 numbers.

DATEYIME and TIMESTAMP types

In addition to the date and time data types, MySQL also supports two mixed types, DATEYIME and TIMESTAMP. They can store dates and times as individual values. These two types are typically used to automatically store timestamps containing the current date and time and can play a good role 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 type TIMESTAMP, or if we are assigned a null value. MySQL automatically populates it with the current date and time of the system.

Compound type

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 considered different data types. An ENUM type allows only one value to be fetched from a collection; the SET type allows any number of values to be fetched from a collection.

ENUM Typ

The ENUM type is somewhat similar to a single option because only one value is allowed in the collection. It is easy to understand when dealing with mutually exclusive data, such as human gender. The ENUM type field can get a value from the collection or use the null value, and other input will cause MySQL to insert an empty string in the field. In addition, if the case of the inserted value does not match the case of the value in the collection, MySQL automatically converts the case of the inserted value to a value that matches the case in the collection.

ENUM types can be stored as numbers within the system and indexed by numbers starting at 1. An ENUM type can contain up to 65536 elements, one of which is reserved by MySQL to store error messages, which are represented by index 0 or an empty string.

MySQL believes that the values that appear in the collection of ENUM types are legitimate inputs, and any other input will fail. This shows that the location of the error record can be easily found by searching for a row that contains an empty string or a corresponding numeric index of 0.

SET Typ

The SET type is similar to but not the same as the ENUM type. The SET type can get any number of values from a predefined collection. And like the ENUM type, any attempt to insert a non-predefined value into the SET type field will cause MySQL to insert an empty string. If you insert a record with 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. The value in the SET element is stored as a separate sequence of "bits" that represent its corresponding elements. "bits" is a simple and effective way to create a collection of ordered elements. And it also removes duplicate elements, so it is impossible to have two identical elements in the SET type.

To find illegal records from SET type fields, all you need to do is look for lines that contain an empty string or a binary value of 0.

Thank you for reading! About the difference between mysql and sql server to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.