MySQL data type

MySQL has a similar data type as SQL Server. Therefore, it is possible to ignore the same place as SQL Server and learn on the basis of SQL Server.

first, the width of the whole type of display

MySQL data types are somewhat different from SQL Server and are defined in the following basic form:

Integral type (display width)

Note that the correct understanding, in fact, is the display width, because the integer width of the display, the size of the value has no effect, but when the Zerofill is set, when the display of the time to fill 0.

　　

From the above display we see that the width is set to 8, and Zerofill , so that in the query, will be in front of the auto-fill 0. The width can not be set because the integer data type has a default display width and is not set to default.

In addition, even if the display width is set, you can also assign a value that exceeds the display width, and the integer display width is only used to display the fill 0.

second, floating point type and fixed-point type

You can specify the precision of floating-point numbers and fixed points in MySQL

Data type (M,D)

• M: Accuracy, total length of data;
• D: Scale, the length after the decimal point;

There are 3 types of MySQL in this type: Float (6,2), Double (6,2), Decimal (6,2);

The difference is:

1. When precision is not specified, the actual precision is saved by default for Float, double, and decimal is the integer by default;
2. When the scale is not enough, it will be rounded, but the decimal will warn the message.

　　

third, date and time type

MySQL date time All kinds are as follows:

Type	Number of bytes	Range of values	0 value
Year	1	1901~2155	0000
DATE	4	1000-01-01~9999-12-31	0000:00:00
Time	3	-838:59:59~838:59:59	00:00:00
Datetime	8	1000-01-01 00:00:00~9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4	1970-01-01 08:00:01~2038-01-19 11:14:07	00000000000000

　　The date type can be preceded by a d,d to indicate that the day is 24 hours, such as inserting ' 2 15:20:20 ' into the date type;

　　

In addition the timestamp type and datetime should be noted in the absence of input:

1. TimeStamp uses Current_timestamp () and DateTime uses now (to get the current time);
2. When NULL is entered, the system will enter the current date and time of the system;
3. When there is no input, the system will enter the current date and time of the system;

iv. type of string

In MySQL, string types include char, VARCHAR, BLOB, TEXT, ENUM, SET.

　　1, char and varchar

It is defined in the following way:

String Type (M)

This m is the length meaning, the length of the string inserted into the column will not be allowed to exceed the length specified by M. In addition, because MySQL specifies a character set when building a library, there is no data type such as nchar, nvarchar, ntext.

• char maximum 255;
• VarChar is the longest preferable 65535;

　　2. TEXT

The text is divided into 4 types, unlike SQL Server:

Type	Allowable length	Storage space
Tinytext	0~255 bytes	Length of Value + 2 bytes
TEXT	0~65535 bytes	Length of Value + 2 bytes
Mediumtext	0~167772150 bytes	Length of Value + 3 bytes
Longtext	0~4294967295 bytes	Length of Value + 4 bytes

3. Enum type

An enum type (enum type), which, like the concept of C #, specifies a range of values when defined.

Attribute name ENUM (' value 1',' value 2',' value 3'... ' Value N ')

• An enum has a NOT NULL property whose default value is the first element of the list of values;
• Enum has no not NULL, the enum type allows NULL to be inserted, and NULL is the default value;

　　

CREATE TABLETest4 (Sex ENUM ('male','female')); INSERT  intoTest4VALUES('male'); INSERT  intoTest4VALUES('ye');--This is an error. 　　 SELECT *  fromTest4;

　　4. Set type

When you create a table, you specify a range of values for the set type.

SET (' value 1',' value 2',' value 3'...,'  value n')

What is the difference between it and the enum?

　　

is basically the multiple-choice enum.

　　5. Binary type

A binary type is a data type that stores binary data in a database. Binary types include binary, VARBINARY, BIT, Tinyblob, BLOB, Mediumblob, Longblob.

Type	Range of values
BINARY (M)	The number of bytes is M, which allows a fixed-length binary string of lengths of 0~m
VARBINARY (M)	Allows variable-length binary string lengths of 0~m and 1 bytes for the length of the value
BIT (M)	M-bit binary data with a m maximum value of 64
Tinyblob	Variable-length binary data, up to 255 bytes
Blob	Variable-length binary data, up to 2 of 16 squares-1 bytes
Mediumblob	Variable-length binary data, up to 2 of 24 squares-1 bytes
Longblob	Variable-length binary data, up to 2 of 32 squares-1 bytes

　　1. Binary and varbinary

The only difference between the two is that binary will fill in when the length is not enough.

　　2. Bit type

The bit type is very different from SQL Server. It is defined in the following way:

BIT M

where "M" specifies that the maximum byte length of the binary is m,m the maximum value is 64. such as bit (4) is the data type bit type, the length is 4. It can store a value of 0-15. Since it becomes binary, the value of 15 is 1111.

When querying bit type data, use Bin (field name +0) to convert the value to binary display.

　　

CREATE TABLE BIT (4)); INSERT  into VALUES (a); SELECT BIN (BB+0 from Test6;

The above results are displayed in binary, with a maximum length of 4.

　　3. Blob type

A BLOB type is a special type of binary. BLOBs can store data in large volumes of binary data, slices, videos, and so on. Blob types include Tinyblob, blobs, Mediumblob, and Longblob. The difference between them is only the maximum length difference.

v. Choice of data Types

1. Integer: Determined according to the maximum value to be displayed;
2. Float type: To display decimals. If you want to be accurate to 10 digits after the decimal point, select Double, and you should not choose float. The decimal precision is higher, the floating-point number will appear error, if the precision is high, should select the fixed-point decimal;
3. String type: The difference between fixed length and variable length, char type takes up more space, but processing speed is faster than varchar, if the length changes little, such as the identity card number, it is best to choose the char type. For comment strings, it is best to choose varchar;
4. Time: According to the need to display the type, especially timestamp, if the time required to display and the timezone corresponding, you should choose Timestamp;
5. Enum types and set types: different lengths, the enum type can be up to 65,535 members, and the set type has a maximum of 64 members. and enum can only be selected, and set type can be more than one choice;
6. Text type and BLOB type: Text can store only character data, and blobs can store binary data. If it is plain text, it is suitable for text. If it is a picture, etc. suitable for depositing binary;

Vi. Summary of issues

　　1, Storage path problems

In MySQL, this symbol is filtered if the "\" symbol is used in the path. The workaround is to replace "\" with "/" or "\" in the path. This way, MySQL does not automatically filter the delimiters in the path.

　　2. Boolean type in MySQL

There is no bool or Boolean type in MySQL, but to support the SQL standard, you can also define a bool or Boolean type, but the bool or Boolean type is finally converted to tinyint (1), which means that in MySQL, The Boolean type is actually tinyint (1).

　　3. How to store JPG images or MP3 music in MySQL

In general, the database does not directly store pictures and audio files, but the path of the picture or audio files, if in special circumstances need to store pictures and audio files in the MySQL database, you can choose the Blob type;

MySQL data type