7 Data structures

Data Structure

The storage engine determines the type of table, and the data stored in the table has different types, each data type has its own width, but the width is optional

Detailed reference Link: http://www.runoob.com/mysql/mysql-data-types.html

MySQL common data types are summarized as follows:

#1. Numbers:integer: tinyint int bigint decimal: float: Less accurate when the number of bits is short: inaccurate when the bit is longer0.000001230123123123Deposit into:0.000001230000decimal: (If using decimal, the recommended decimal) The precise internal principle is stored as a string#2. String:   Char (Ten ): Simple rough, wasted space, fast access speed fixed root saved into root000000 varchar: precision, space saving, slow access speed  SQL optimization: creating Table , the type of the fixed length is put forward, and the length of the future is longer, such as the sex ratio, address or descriptive information    >255 characters, the file path is stored in the database. Compare slices, videos, etc. find a file server, only the path or URL is stored in the database. #3. Time Type:most used: DateTime#4. Enumeration types and collection typesEnum and set

First, numeric type

Integer type: TINYINT SMALLINT mediumint INT BIGINT

tinyint [(m)] [unsigned] [zerofill] small integer, data type is used to hold some range of integer numeric ranges:            signed                :-128 ~ 127 unsigned             :                             There is no boolean value in 255 ps:mysql, constructed using tinyint (1).

=============================================================
Int[(M)] [Unsigned][zerofill]

Integers, data types are used to hold some range of integer numeric ranges:            signed:                    -2147483648 ~ 2147483647 unsigned            :                    0 ~ 4294967295

=============================================
bigint [(M)][unsigned][zerofill]

Large integers, data types are used to hold some range of integer numeric ranges:            signed:                    -9223372036854775808 ~ 9223372036854775807 unsigned            :                    0  ~  18446744073709551615

Verification 1:有符号和无符号tinyint

============ signed tinyint==============#Create a database DB4CREATE Database DB4 charset UTF8;#switch to the current DB4 databaseMysql>Use DB4;#Create T1 specified x field tinyint data type (default is signed)Mysql>CREATE table t1 (x tinyint);#Verify, insert-1 This numbermysql> INSERT INTO T1 values (-1);#query table record, query successful (prove default is signed type)Mysql> SELECT * fromT1;+------+| X |+------+| -1 |+------+#perform the following actions, and you will find an error. Because the signed range is in ( -128,127)mysql> INSERT INTO T1 values (-129), (128); ERROR1264 (22003): Out of range value forColumn'x'At row 1============ unsigned tinyint==============#The character that defines the record when the table is created is an unsigned type (0,255), using the unsignedMysql>CREATE TABLE t2 (x tinyint unsigned);#error, out of rangemysql> INSERT into T2 values (-129); ERROR1264 (22003): Out of range value forColumn'x'At row 1#Insert Successfulmysql> INSERT into T2 values (255); Query OK,1 Row Affected (0.00 sec)

Verify that the storage behind the 2:int type is display width, not storage width

mysql> CREATE TABLE t3 (id int (1) unsigned);#inserting 255555 Records is also possible.mysql> INSERT into T3 values (255555); MySQL> select * fromT3;+--------+| ID |+--------+| 255555 |+--------+PS: The above operation can not be verified, then a table to verify with the Zerofill with 0 fill#zerofill with 0 paddingmysql> CREATE table t4 (ID int (5) unsigned zerofill); MySQL> INSERT into T4 value (1); Query OK,1 Row Affected (0.00sec)#The inserted record is 1, but the width of the display is 00001Mysql> SELECT * fromT4;+-------+| ID |+-------+| 00001 |+-------+RowinchSet (0.00 sec)

Note: When you specify a width for this type, you only specify the display width of the query results, regardless of the storage scope, as follows

In fact, we don't have to specify a display width for an integer type, so you can use the default.

The default display width is based on the maximum value plus 1 tinyint The default display width is 4 bits

7 Data Structures