MySql data type overview _ MySQL

MySql data type introduction bitsCN.com

In the past, when using the database, I did not pay much attention to the selection of the column type. now I have a time to summarize it. This article has referred to the MySql5.1 Reference Manual, MySql must know, and so on, and does not have much personal experience. You are welcome to add.

Common MySql data types define what data can be stored in columns and how the data is actually stored. There are roughly three types:

1. Numeric type
2. Date and time
3. String

Numeric type

MySql supports multiple numeric data types with different values. Most numeric data types can be signed or unsigned (BIT unsigned). The default value is signed. NUMERIC Data types include strict NUMERIC data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate NUMERIC data types (FLOAT, REAL, and double precision ). As an extension of the SQL standard, MySQL also supports integer TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type. For the floating-point column type, the single-precision value in MySQL uses 4 bytes, and the double-precision value uses 8 bytes.

DECIMAL and NUMERIC typesIn MySQL, the data type is the same. They are used to save values that must be accurate, such as currency data. When declaring a column of this type, you can (and usually need to) specify the precision and scale. for example: salary DECIMAL (5, 2)

FLOAT type and DOUBLE typeUsed to represent the approximate numeric data type.

　　Pay attention to floating point and fixed point

1. The floating point number has an error.
2. Data that is sensitive to currency and other precision should be expressed and stored by a specific number of points.
3. During programming, try to avoid comparing floating point numbers.
4. Pay attention to the processing of special values in floating point numbers.

Date and time data types

Data type	Description
DATA	Indicates 1000-01-01 ~ The date between 9999-12-31 in the format of yyyy-mm-dd.
TIME	Format: HH: MM: SS
DATATIME	Combination of DATA and TIME
TIMESTAMP	The function is the same as DATETIME, but the range is small.
YEAR	If two digits are used, the value ranges from 1970 ~ May 2069. If four digits are used, the range is 1901 ~ May 2155.

String type

There are two basic string types: fixed-length and variable-length. CHAR is a type of top-length, and VARCHAR, BLOB, and TEXT are variable-length types. The storage requirements for each type depend on the actual length of the column value (useLInstead of the maximum possible size of this type. For example, the VARCHAR (10) column can contain strings with a maximum length of 10. The actual storage requirement is string (L), Plus the length of a record string in bytes. For the string 'ABC ',LIt is 4 and the storage needs 5 bytes. For a detailed description of the string type, see encoding.

CHAR: Accept a fixed-length string, which is specified during table creation. Char cannot exceed the specified number of characters. the allocated space is as much as the specified one.

VARCHA: Only the specified data is saved (the additional data is not saved ). You can specify the maximum length of a varchar.

Differences:

1. The storage length is different.
2. Processing char is much faster than varchar.
3. MySql does not allow varchar indexing.
4. The space at the end of char is not saved, that is, truncation.
5. Compared with CHAR, the VARCHAR value only saves the number of characters and adds a byte to record the length (if the declared length of a column exceeds 255, two bytes are used ).

Value	CHAR (4)	Storage requirements	VARCHAR (4)	Storage requirements
''	''	4 bytes	''	1 byte
'AB'	'AB'	4 bytes	'AB'	3 bytes
'ABCD'	'ABCD'	4 bytes	'ABCD'	5 bytes
'Abcdefgh'	'ABCD'	4 bytes	'ABCD'	5 bytes

The following example illustrates the problem at the tail stage:
Mysql> create table vc (v varchar (4), c char (4 ));

Mysql> insert into vc values ('AB', 'AB ');

Mysql> select concat (v, '+'), concat (c, '+') from vc;
+ --------------- +
| Concat (v, '+') | concat (c, '+') |
+ --------------- +
| AB + |
+ --------------- +

Tags: MySql Green Channel: Good article top follow my favorites this article contact me bitsCN.com