Mysql Data Type and usage

MySQL supports multiple column types: numeric, date/time, and string (character. This chapter first gives an overview of these column types, and then describes in more detail the types of various columns and the summary of column type storage requirements. The overview is simple. For detailed information about specific column types, see detailed descriptions, such as the format that can be used when a value is specified.

MySQL supports spatial data processing extensions.

The following conventions are used for column type descriptions:

M: Indicates the maximum display width. The maximum valid display width is 255.

MIndicates the maximum display width. The maximum valid display width is 255. The display width is irrelevant to the storage size or the range of values in the type.

D: Applicable to floating point and fixed point types, and represents the number of digits after the decimal point. The maximum value is 30, but it should not be greaterM-2.

Square brackets ('[' and ']') indicate the optional parts.

If ZEROFILL is specified for a value column, MySQL automatically adds the UNSIGNED attribute to the column.

SERIAL is an alias of bigint unsigned not null AUTO_INCREMENT UNIQUE.

In the integer column definition, serial default value is an alias of not null AUTO_INCREMENT UNIQUE.

 

BIT [(M)]

Bit Field type.MThe number of digits of each value, ranging from 1 to 64. IfMOmitted. The default value is 1.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is-128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN

Is a synonym for TINYINT (1. The zero value is considered false. The non-zero value is true.

 

INT [(M)] [UNSIGNED] [ZEROFILL]

An integer of the normal size. The signed range is-2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is-9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

 

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.

Type	Bytes	Minimum value	Maximum Value
		(Signed/unsigned)	(Signed/unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

 

CHAR and VARCHAR types

CHAR and VARCHAR types are similar, but they are stored and retrieved in different ways. Their maximum length and whether the trailing space is retained are also different. Case-insensitive conversion is not performed during storage or retrieval.

The length of the CHAR and VARCHAR types declaration indicates the maximum number of characters you want to save. For example, CHAR (30) can occupy 30 characters.

The length of the CHAR column is fixed to the length declared during table creation. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces on the right of them to reach the specified length. When the CHAR value is retrieved, the trailing space is deleted. Case-insensitive conversion is not performed during storage or retrieval.

The value in the VARCHAR column is a variable-length string. Length can be set to a value between 0 and 65,535. (The maximum valid length of a VARCHAR is determined by the maximum row size and the character set used. The total length is 65,532 bytes ).

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 ).

The VARCHAR value is not filled when it is saved. When the value is saved and retrieved, the space at the end is retained, which complies with the standard SQL.

If the value assigned to a CHAR or VARCHAR column exceeds the maximum length of the column, crop the value to make it suitable. If the character to be dropped is not a space, a warning is generated. If you crop non-space characters, it will cause an error (rather than warning) and disable value insertion by using strict SQL mode. See section 5.3.2 "SQL SERVER mode ".

The following table shows the results of saving various string values to the CHAR (4) and VARCHAR (4) columns, indicating the differences between CHAR and VARCHAR:

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

Note that the value of the last row in the above table only appliesDo not use strict ModeIf MySQL runs in strict mode, the value exceeds the column length limit.NoSaveAnd an error occurs.

The values retrieved from the CHAR (4) and VARCHAR (4) columns are not always the same, because spaces at the end of the CHAR column are deleted during retrieval.

The following example shows the difference:

Mysql>Create table vc (v VARCHAR (4), c CHAR (4 ));

Query OK, 0 rows affected (0.02 sec)

 

Mysql>Insert into vc VALUES ('AB', 'AB ');

Query OK, 1 row affected (0.00 sec)

 

Mysql>Select concat (v, '+'), CONCAT (c, '+') FROM vc;

+ ---------------- +

| CONCAT (v, '+') | CONCAT (c, '+') |

+ ---------------- +

| AB + |

+ ---------------- +

1 row in set (0.00 sec)

Sort and compare the values in the CHAR and VARCHAR columns Based on the character set proofreading rules assigned to the columns.

Note that all MySQL proofreading rules belong to the PADSPACE class. This indicates that no trailing space is required when comparing all CHAR and VARCHAR values in MySQL. For example:

Mysql>Create table names (myname CHAR (10), yourname VARCHAR (10 ));

Query OK, 0 rows affected (0.09 sec)

 

Mysql>Insert into names VALUES ('monty ', 'monty ');

Query OK, 1 row affected (0.00 sec)

 

Mysql>SELECT myname = 'monty ', yourname = 'monty' FROM names;

+ -------------------- + ---------------------- +

| Myname = 'monty '| yourname = 'monty' |

+ -------------------- + ---------------------- +

| 1 | 1 |

+ -------------------- + ---------------------- +

1 row in set (0.00 sec)

Note that this applies to all MySQL versions and is not affected by the SQL Server mode.

When trailing padding characters are cropped or compared, ignore them. If the column index requires a unique value, inserting a value that only contains different characters in the column will result in a duplicate key value error.

Char byte is the alias of char binary. This is to ensure compatibility.

The ASCII attribute is assigned to the CHAR column with the latin1 character set. The UNICODE attribute is assigned to the ucs2 character set.

 

Reprinted please indicate original link: http://blog.csdn.net/wujunokay/article/details/25086957