Mysql支持的数据类型(列类型总结)金沙官网线上:

数值类型

数值类型又分为整数型与小数型

 MySQL 提供了8个基本的字符串类型,分别:CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 各SET等多种字符串类型。

CHAR与VARCHAR类型

CHAR表示定长,如CHAR(10)表示可以存储10个字符,最大长度为255个字符,当保存CHAR值时,在它们的右边填充空格以达到指定的长度。当检索到CHAR值时,尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR表示变长,如VARCHAR(10)表示可以存储10个字符,最大长度为65535个字符(VARCHAR的最大有效长度由最大行大小和使用的字符集确定,实际最大长度为65532个字符),与CHAR不同的是,VARCHAR值保存时只保存需要的字符数,另加一个字节表示字符的长度(如果长度超过255,则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

金沙官网线上 1

请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误。

定长的磁盘空间比较浪费, 但是效率高

相反的,变长的磁盘空间比较节省, 但是效率低

  MySQL 用 DATE 和 TEAR 类型存储简单的日期值,使用 TIME 类型存储时间值。这些类型可以描述为字符串或不带分隔符的整数序列。如果描述为字符串,

小数型

SQL中将小数型又分为浮点型和定点型两种

浮点型:小数点浮动,精度有限,并且会丢失精度(会进行四舍五入),精度位数大概在7位

定点型:小数点固定,精度固定,不会丢失精度,精度位数大概在15位

浮点型

对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节。

FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围)。MySQL还支持可选的只用于确定存储大小的精度规定0到23的精度对应FLOAT列的4字节单精度。24到53的精度对应DOUBLE列的8字节双精度。

一个FLOAT类型的数字,允许的值为-3.402823466E+38至-1.175494351E-38,0,1.175494351E-38至3.402823466E+38,这些是基于IEEE标准的理论值,实际范围根据硬件或操作系统可能会稍微小一些。

MySQL允许使用非标准语法:FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里,“(M,D)”表示该值一共显示M位整数,其中D位位于小数点后面。例如,定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入,因此如果在FLOAT(7,4)列内插入999.00009,近似结果是999.0001。

下面以FLOAT为例子,DOUBLE类似

mysql> CREATE TABLE t_float(
 float_1 FLOAT,
 float_2 FLOAT(10, 2),
 float_3 FLOAT(6, 2));
Query OK, 0 rows affected

mysql> DESC t_float;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| float_1 | float       | YES  |     | NULL    |       |
| float_2 | float(10,2) | YES  |     | NULL    |       |
| float_3 | float(6,2)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set

mysql> INSERT INTO t_float VALUES (1000.10,1000.10,1000.10); -- 正常数据
Query OK, 1 row affected

mysql> INSERT INTO t_float VALUES (1234567890,12345678.90,1234.56); -- 正常数据
Query OK, 1 row affected

mysql> INSERT INTO t_float VALUES (3e38,3.01e7,1234.56); -- 可以用科学计数法表示
Query OK, 1 row affected

mysql> INSERT INTO t_float VALUES (9999999999,99999999.99,9999.99); -- 插入最大值,超过精度位数时系统会进行四舍五入
Query OK, 1 row affected

mysql> SELECT * FROM t_float;
+-------------+--------------+---------+
| float_1     | float_2      | float_3 |
+-------------+--------------+---------+
|      1000.1 |      1000.10 | 1000.10 |
|  1234570000 |  12345679.00 | 1234.56 |
|        3e38 |  30100000.00 | 1234.56 |
| 10000000000 | 100000000.00 | 9999.99 |
+-------------+--------------+---------+
4 rows in set

在插入浮点型数据的时候,整型部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入)

mysql> INSERT INTO t_float VALUES (123456,1234.123456768,123.9876543); -- 小数部分超出,四舍五入
Query OK, 1 row affected

mysql> INSERT INTO t_float VALUES (123456,1234.12,12345.56);
1264 - Out of range value for column 'float_3' at row 1

mysql> SELECT * FROM t_float;
+-------------+--------------+---------+
| float_1     | float_2      | float_3 |
+-------------+--------------+---------+
|      1000.1 |      1000.10 | 1000.10 |
|  1234570000 |  12345679.00 | 1234.56 |
|        3e38 |  30100000.00 | 1234.56 |
| 10000000000 | 100000000.00 | 9999.99 |
|      123456 |      1234.12 |  123.99 |
+-------------+--------------+---------+
5 rows in set (0.00 sec)

超出精度范围的浮点数,系统会自动进行四舍五入,如果因为系统进位导致整数部分超出指定的长度,这也是被允许的,例如上面的第4条记录。

为了保证最大可能的可移植性,需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION,不规定精度或位数。

定点型

DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值,例如货币数据。当声明该类型的列时,可以(并且通常要)指定精度和标度,如

salary DECIMAL(5,2)

在该例子中,5是精度,2是标度。精度表示保存值的主要位数,标度表示小数点后面可以保存的位数。

在标准SQL中,语法DECIMAL(M)等价于DECIMAL(M,0)。同样,语法DECIMAL等价于DECIMAL(M,0),可以通过计算确定M的值。在MySQL中支持DECIMAL和NUMERIC数据类型的变量形式。M默认值是10。

DECIMAL或NUMERIC的最大位数是65,但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。

mysql> CREATE TABLE t_decimal (
 float_1 FLOAT (10, 2),
 decimal_1 DECIMAL (10, 2));
Query OK, 0 rows affected

mysql> INSERT INTO t_decimal VALUES (12345678.90,12345678.90); -- 正常数据
Query OK, 1 row affected

mysql> INSERT INTO t_decimal VALUES (1234.123456,1234.1234356); -- 小数部分四舍五入
Query OK, 1 row affected

mysql> INSERT INTO t_decimal VALUES (99999999.99,99999999.99); -- 最大值
Query OK, 1 row affected

mysql> INSERT INTO t_decimal VALUES (99999999.99,99999999.999); -- 进位超出指定位数,不允许
1264 - Out of range value for column 'decimal_1' at row 1

mysql> SELECT * FROM t_decimal;
+--------------+-------------+
| float_1      | decimal_1   |
+--------------+-------------+
|  12345679.00 | 12345678.90 |
|      1234.12 |     1234.12 |
| 100000000.00 | 99999999.99 |
+--------------+-------------+
3 rows in set (0.00 sec)

万一我们需要对一个字段存储一个超出许可范围的数字,MySQL 会根据允许范围最接近它的一端截短后再进行存储。还有一个比较特别的地方是,

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

M 指出最大的显示尺寸。最大的合法的显示尺寸是 255 。
D
适用于浮点类型并且指出跟随在十进制小数点后的数码的数量。最大可能的值是30,但是应该不大于M-2。
方括号(“[”和“]”)指出可选的类型修饰符的部分。

ENUM类型

ENUM是一个字符串对象,其值来自表创建时在列规定中显式枚举的一列值,每个枚举值有一个索引(NULL值的索引是NULL),从1开始编号

mysql> CREATE TABLE t_enum ( -- 创建枚举表
 gender ENUM('man', 'woman', 'unknow'));

Query OK, 0 rows affected

mysql> INSERT INTO t_enum VALUES ('man');
Query OK, 1 row affected

mysql> INSERT INTO t_enum VALUES ('woman');
Query OK, 1 row affected

mysql> INSERT INTO t_enum VALUES ('haha'); -- 不在枚举定义的值里面,不允许插入,起到规范数据的作用
1265 - Data truncated for column 'gender' at row 1

如果在数值上下文中检索一个ENUM值,将返回列值的索引。例如,你可以这样从ENUM列搜索数值值:

mysql> SELECT gender + 0, gender FROM t_enum;
+------------+--------+
| gender + 0 | gender |
+------------+--------+
|          1 | man    |
|          2 | woman  |
+------------+--------+
2 rows in set

由于枚举存储的是数值,所以可以直接插入数值

mysql> INSERT INTO t_enum VALUES (1), (2); -- 直接插入数值
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT gender + 0, gender FROM t_enum;
+------------+--------+
| gender + 0 | gender |
+------------+--------+
|          1 | man    |
|          2 | woman  |
|          1 | man    |
|          2 | woman  |
+------------+--------+
4 rows in set

 

整数型

下面的表显示了需要的每个整数类型的存储和范围

金沙官网线上 2

创建一张表

mysql> CREATE TABLE t_int (
 int_1 TINYINT,
 int_2 SMALLINT,
 int_3 MEDIUMINT,
 int_4 INT,
 int_5 BIGINT);
Query OK, 0 rows affected

mysql> DESC t_int;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| int_1 | tinyint(4)   | YES  |     | NULL    |       |
| int_2 | smallint(6)  | YES  |     | NULL    |       |
| int_3 | mediumint(9) | YES  |     | NULL    |       |
| int_4 | int(11)      | YES  |     | NULL    |       |
| int_5 | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set

插入数据,只能插入整型,数据类型不正确或者超出范围都会导致插入数据失败

mysql> INSERT INTO t_int VALUES(100, 100, 100, 100, 100);
Query OK, 1 row affected

mysql> INSERT INTO t_int VALUES('a', 'b', 100, 200, 300); -- 数据类型不正确
1366 - Incorrect integer value: 'a' for column 'int_1' at row 1
mysql> INSERT INTO t_int VALUES(255, 10000, 1000, 100000, 1000000); -- 超出范围
1264 - Out of range value for column 'int_1' at row 1
mysql> SELECT * FROM t_int;
+-------+-------+-------+-------+-------+
| int_1 | int_2 | int_3 | int_4 | int_5 |
+-------+-------+-------+-------+-------+
|   100 |   100 |   100 |   100 |   100 |
+-------+-------+-------+-------+-------+
1 row in set

SQL中的数值类型全部都是默认有符号,分正负,如果要使用无符号数据,则需要用UNSIGNED关键字对数据类型进行限定

mysql> ALTER TABLE t_int ADD COLUMN int_6 TINYINT UNSIGNED; -- 无符号类型
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC t_int;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| int_1 | tinyint(4)          | YES  |     | NULL    |       |
| int_2 | smallint(6)         | YES  |     | NULL    |       |
| int_3 | mediumint(9)        | YES  |     | NULL    |       |
| int_4 | int(11)             | YES  |     | NULL    |       |
| int_5 | bigint(20)          | YES  |     | NULL    |       |
| int_6 | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
6 rows in set

mysql> INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255); -- TINYINT无符号类型的取值范围为0-255
Query OK, 1 row affected

mysql> SELECT * FROM t_int;
+-------+-------+--------+--------+---------+-------+
| int_1 | int_2 | int_3  | int_4  | int_5   | int_6 |
+-------+-------+--------+--------+---------+-------+
|   100 |   100 |    100 |    100 |     100 | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 |
+-------+-------+--------+--------+---------+-------+
2 rows in set

给字段加了无符号的限定后,数据插入成功了,在查看表结构的时候,发现每个字段的数据类型之后都会自带一个括号,里面有指定的数字,这个数字表示数据最终显示的位数,例如-123表示显示4位(包括符号位),255表示显示3位,实际上这个数字没有什么特别的含义,只是默认告诉用户可以显示的宽度而已,并不会改变数据的大小。

mysql> ALTER TABLE t_int ADD COLUMN int_7 TINYINT(1) UNSIGNED; -- 显示指定显示宽度
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255, 255);
Query OK, 1 row affected

mysql> SELECT * FROM t_int;
+-------+-------+--------+--------+---------+-------+-------+
| int_1 | int_2 | int_3  | int_4  | int_5   | int_6 | int_7 |
+-------+-------+--------+--------+---------+-------+-------+
|   100 |   100 |    100 |    100 |     100 | NULL  | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 |   255 |
+-------+-------+--------+--------+---------+-------+-------+
3 rows in set

显示宽度的意义在于当数据不够显示宽度的时候,可以使用ZEROFILL为数字列进行零填充,这时MySQL自动为该列添加UNSIGNED属性

mysql> ALTER TABLE t_int ADD COLUMN int_8 TINYINT(2) UNSIGNED ZEROFILL;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t_int VALUES(127, 10000, 100000, 100000, 1000000, 255, 255, 1);
Query OK, 1 row affected

mysql> SELECT * FROM t_int;
+-------+-------+--------+--------+---------+-------+-------+-------+
| int_1 | int_2 | int_3  | int_4  | int_5   | int_6 | int_7 | int_8 |
+-------+-------+--------+--------+---------+-------+-------+-------+
|   100 |   100 |    100 |    100 |     100 | NULL  | NULL  | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 | NULL  | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 |   255 | NULL  |
|   127 | 10000 | 100000 | 100000 | 1000000 |   255 |   255 |   01  |
+-------+-------+--------+--------+---------+-------+-------+-------+
4 rows in set

MySQL客户端如Navicat中可能不能正确显示零填充的效果,可以使用命令行窗口进行查看。

    VARBINARY(M)                   允许长度0-M个字节的定长字节符串,值的长度+1个字节

BLOB与TEXT类型

通常超过255个字符的数据使用大文本进行存储,根据存储的数据格式分为BLOB与TEXT,BLOB用来存储二进制数据(少用),TEXT用来存储文字。

BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。

 

MySQL支持多种列类型:数值类型、日期/时间类型和字符串(字符)类型。

 

SET类型

集合跟枚举很类似,实际存储的是数值(SET最多可以有64个不同的成员),而不是字符串,可以把集合看成是多选框,枚举看成是单选

mysql> CREATE TABLE t_set (
 letter SET ('a', 'b', 'c', 'd'));
Query OK, 0 rows affected

mysql> INSERT INTO t_set VALUES ('a,b');
Query OK, 1 row affected

mysql> INSERT INTO t_set VALUES ('a,b,c');
Query OK, 1 row affected

MySQL用数字保存SET值,所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值,检索的值的位设置对应组成列值的SET成员,例如,你可以这样从一个SET列检索数值值

mysql> SELECT letter + 0, letter FROM t_set;
+------------+--------+
| letter + 0 | letter |
+------------+--------+
|          3 | a,b    |
|          7 | a,b,c  |
+------------+--------+
2 rows in set

如果你为该列分配一个值9,其二进制形式为1001,因此第1个和第4个SET值成员'a'和'd'被选择,结果值为 'a,d'。
对于包含多个SET元素的值,当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时,值中的每个元素出现一次,根据表创建时指定的顺序列出元素。例如,插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d'

mysql> INSERT INTO t_set VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT letter + 0, letter FROM t_set; -- 检索时所有的值将显示为a,d
+------------+--------+
| letter + 0 | letter |
+------------+--------+
|          3 | a,b    |
|          7 | a,b,c  |
|          9 | a,d    |
|          9 | a,d    |
|          9 | a,d    |
|          9 | a,d    |
|          9 | a,d    |
+------------+--------+
7 rows in set

通常情况,可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值:

mysql> SELECT * FROM t_set WHERE FIND_IN_SET('a',letter) > 0; -- 表示找出t_set包含a的成员
+--------+
| letter |
+--------+
| a,b    |
| a,b,c  |
| a,d    |
| a,d    |
| a,d    |
| a,d    |
| a,d    |
+--------+
7 rows in set

mysql> SELECT * FROM t_set WHERE letter LIKE '%a%'; -- 所有set中包含a的成员,甚至是在另一个SET成员的子字符串中
+--------+
| letter |
+--------+
| a,b    |
| a,b,c  |
| a,d    |
| a,d    |
| a,d    |
| a,d    |
| a,d    |
+--------+
7 rows in set

 

用较小的存储代价换来较高的数据库性能。

BINARY与VARBINARY类型

 

   ENUM 类型在系统内部可以存储为数字,并且从 1 开始用数字做索引。一个 ENUM 类型最多可以包含 65536 个元素,其中一个元素被 MySQL 保留,用来存储错误信息,

时间日期类型

表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值。

金沙官网线上 3

mysql> CREATE TABLE t_date (
 id INT,
 date_1 DATETIME,
 date_2 TIMESTAMP,
 date_3 DATE,
 date_4 TIME,
 date_5 YEAR);
Query OK, 0 rows affected

mysql> DESC t_date; -- timestamap不允许为空,有个默认值,为当前时间
+--------+-----------+------+-----+-------------------+-----------------------------+
| Field  | Type      | Null | Key | Default           | Extra                       |
+--------+-----------+------+-----+-------------------+-----------------------------+
| id     | int(11)   | YES  |     | NULL              |                             |
| date_1 | datetime  | YES  |     | NULL              |                             |
| date_2 | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| date_3 | date      | YES  |     | NULL              |                             |
| date_4 | time      | YES  |     | NULL              |                             |
| date_5 | year(4)   | YES  |     | NULL              |                             |
+--------+-----------+------+-----+-------------------+-----------------------------+
6 rows in set

mysql> INSERT INTO t_date VALUES(1, '2018-6-24 15:17:58', '2018-6-24 15:18:14', '2018-6-24', '15:18:27', '2018'); -- 正常数据
Query OK, 1 row affected

mysql> INSERT INTO t_date VALUES(2, '2018-6-24 15:17:58', NULL, '2018-6-24', '15:18:27', '2018'); -- timestamp为空NULL,会自动赋当前时间
Query OK, 1 row affected

mysql> SELECT * FROM t_date;
+----+---------------------+---------------------+------------+----------+--------+
| id | date_1              | date_2              | date_3     | date_4   | date_5 |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2018 |
|  2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 |   2018 |
+----+---------------------+---------------------+------------+----------+--------+
2 rows in set

'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串。允许“不严格”语法:任何标点符都可以用做日期部分或时间部分之间的间割符。例如,'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 11*30*45'和'98@12@31 11^30^45'是等价的

'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。这里也允许使用“不严格的”语法。例如,'98-12-31'、'98.12.31'、'98/12/31'和'98@12@31'是等价的。

'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串,假定字符串对于日期类型是有意义的。例如,'19970523'和'970523'被解释为 '1997-05-23',但'971332'是不合法的(它有一个没有意义的月和日部分),将变为'0000-00-00'。

YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字,假定数字对于日期类型是有意义的。例如,19830905132800和830905132800被解释为 '1983-09-05 13:28:00'。

YYYYMMDD或YYMMDD格式的数字,假定数字对于日期类型是有意义的。例如,19830905和830905被解释为'1983-09-05'。

mysql> INSERT INTO t_date VALUES(3, '2018@6@24 15^17^58', '2018#6#24 15+18+14', '2018/6/24', '15:18:27', '2018'); -- 用不同的标点符号做分隔符
Query OK, 1 row affected

mysql> SET sql_mode = ''; -- 设置非严格模式,只在该会话中有效
Query OK, 0 rows affected

mysql> SELECT @@sql_mode; -- 查看设置是否生效
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set

mysql> INSERT INTO t_date VALUES(4, 20180624151758, 180624151814, 20181324, '15:18:27', '2018'); -- 插入20181324时会自动转换为零值,前提是要设置非严格模式,否则报错
Query OK, 1 row affected

mysql> SELECT * FROM t_date;
+----+---------------------+---------------------+------------+----------+--------+
| id | date_1              | date_2              | date_3     | date_4   | date_5 |
+----+---------------------+---------------------+------------+----------+--------+
|  1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2018 |
|  2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 |   2018 |
|  3 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2018 |
|  4 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 0000-00-00 | 15:18:27 |   2018 |
+----+---------------------+---------------------+------------+----------+--------+
4 rows in set

YEAR类型可以用二位数,也可以用四位数表示

四位字符串,范围为'1901'到'2155'。

四位数字,范围为1901到2155。

两位字符串,范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。

两位整数,范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同,因为你不能直接将零指定为一个字符串'0'或'00'或它被解释为0000。

mysql> INSERT INTO t_date VALUES(6, '2018-6-24 15:17:58', '2018-6-24 15:18:14', '2018-6-24', '15:18:27', 2100);
Query OK, 1 row affected

mysql> INSERT INTO t_date VALUES(7, '2018-6-24 15:17:58', '2018-6-24 15:18:14', '2018-6-24', '15:18:27', 2166); -- 超出范围转换为零值
Query OK, 1 row affected

mysql> INSERT INTO t_date VALUES(8, '2018-6-24 15:17:58', '2018-6-24 15:18:14', '2018-6-24', '15:18:27', '69');
Query OK, 1 row affected

mysql> INSERT INTO t_date VALUES(9, '2018-6-24 15:17:58', '2018-6-24 15:18:14', '2018-6-24', '15:18:27', '70');
Query OK, 1 row affected

mysql> SELECT * FROM t_date;
+------+---------------------+---------------------+------------+----------+--------+
| id   | date_1              | date_2              | date_3     | date_4   | date_5 |
+------+---------------------+---------------------+------------+----------+--------+
|    1 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2018 |
|    2 | 2018-06-24 15:17:58 | 2018-06-24 15:35:55 | 2018-06-24 | 15:18:27 |   2018 |
|    3 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2018 |
|    4 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 0000-00-00 | 15:18:27 |   2018 |
|    6 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2100 |
|    7 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   0000 |
|    8 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   2069 |
|    9 | 2018-06-24 15:17:58 | 2018-06-24 15:18:14 | 2018-06-24 | 15:18:27 |   1970 |
+------+---------------------+---------------------+------------+----------+--------+
8 rows in set (0.00 sec)

 TIMESTAMP  4       1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值,时间戳

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

 DATE       4        1000-01-01/9999-12-31 YYYY-MM-DD    日期值 

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
一个小整数。有符号的范围是-32768到32767,无符号的范围是0到65535。

 类型     大小(字节)     范围               格式          用途 

注意,如果你指定一个了为ZEROFILL,MySQL将为该列自动地增加UNSIGNED属性。

FLOAT[(M,D)] [ZEROFILL]
一个小(单精密)浮点数字。不能无符号。允许的值是-3.402823466E+38到-1.175494351E-38,0 和1.175494351E-38到3.402823466E+38。M是显示宽度而D是小数的位数。没有参数的FLOAT或有<24 的一个参数表示一个单精密浮点数字。

一.数值类型

而计数方法表示小数点后数字的位数。比如语句 DECIMAL(7,3) 规定了存储的值不会超过 7 位数字,并且小数点后不超过 3 位。

MySQL 会在不合规定的值插入表前自动修改为 0。

VARCHAR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

  在 MySQL 中支持的 5 个主要整数类型是 TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。这些类型在很大程度上是相同的,只有它们存储的值的大小是不相同的。

DECIMAL 数据类型用于精度要求非常高的计算中,这种类型允许指定数值的精度和计数方法作为选择参数。精度在这里指为这个值保存的有效数字的总个数,

比指定类型支持的最大范围大的值将被自动截短。

 SET 类型与 ENUM 类型相似但不相同。SET 类型可以从预定义的集合中取得任意数量的值。并且与 ENUM 类型相同的是任何试图在 SET 类型字段中插入非预定义的值都会使 

    MEDIUMTEXT   0-16 777 215字节 中等长度文本数据 

MEDIUMINT      3字节    (-8 388 608,8 388 607) (0,16 777 215)      大整数值 

整数类型        字节       范围(有符号)      范围(无符号)          用途 

DOUBLE         8字节 (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度浮点数值 

 

这两种类型通常用于自动存储包含当前日期和时间的时间戳,并可在需要执行大量数据库事务和需要建立一个调试和审查用途的审计跟踪的应用程序中发挥良好作用。

LONGTEXT
一个BLOB或TEXT列,最大长度为4294967295(2^32-1)个字符。

除此之外的输入将会使 MySQL 在这个字段中插入一个空字符串。另外如果插入值的大小写与集合中值的大小写不匹配,MySQL 会自动使用插入值的大小写转换成与集合中大小写一致的值。

 TIME       3        '-838:59:59'/'838:59:59' HH:MM:SS    时间值或持续时间 

 

DECIMAL[(M[,D])] [ZEROFILL]
一个未压缩(unpack)的浮点数字。不能无符号。行为如同一个CHAR列:“未压缩”意味着数字作为一个字符串被存储,值的每一位使用一个字符。小数点,并且对于负数,“-”符号不在M中计算。如果D是0,值将没有小数点或小数部分。DECIMAL值的最大范围与DOUBLE相同,但是对一个给定的DECIMAL列,实际的范围可以通过M和D的选择被限制。如果D被省略,它被设置为0。如果M被省掉,它被设置为10。注意,在MySQL3.22里,M参数包括符号和小数点。

DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

 

MySQL 插入一个空字符串。如果插入一个即有合法的元素又有非法的元素的记录,MySQL 将会保留合法的元素,除去非法的元素。

这个指示器的方式:CHAR 把这个大小视为值的大小,不长度不足的情况下就用空格补足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度

MySQL 还对日期的年份中的两个数字的值,或是 SQL 语句中为 TEAR 类型输入的两个数字进行最大限度的通译。因为所有 TEAR 类型的值必须用 4 个数字存储。

SET 类型

对于小数点后面的位数超过允许范围的值,MySQL 会自动将它四舍五入为最接近它的值,再插入它。

  对于字段长度要求超过 255 个的情况下,MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小,它们都有不同的子类型。这些大型的数据用于存储文本块或图像、

 

一个大整数。有符号的范围是-9223372036854775808到9223372036854775807,无符号的范围是0到

 

Mysql支持所有标准SQL中的数值类型,其中包括严格数据类型(INTEGER,SMALLINT,DECIMAL,NUMBERIC),以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展。

 

并且它还去除了重复的元素,所以 SET 类型中不可能包含两个相同的元素。

MySQL 试图将 2 个数字的年份转换为 4 个数字的值。把在 00-69 范围内的值转换到 2000-2069 范围内。把 70-99 范围内的值转换到 1970-1979 之内。

NUMERIC(M,D) [ZEROFILL]
这是DECIMAL的一个同义词。

MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。本节首先给出可用类型的一个概述,并且总结每个列类型的存储需求,然后提供每个类中的类型性质的更详细的描述。概述有意简化,更详细的说明应该考虑到有关特定列类型的附加信息,例如你能为其指定值的允许格式。

这个错误值用索引 0 或者一个空字符串表示。

一个 ENUM 类型只允许从一个集合中取得一个值;而 SET 类型允许从一个集合中取得任意多个值。

[NATIONAL] VARCHAR(M) [BINARY]
一个变长字符串。注意:当值被存储时,尾部的空格被删除(这不同于ANSI SQL规范)。M的范围是1 ~ 255个字符。 VARCHAR值根据缺省字符集以大小写不区分的方式排序和比较,除非给出BINARY关键词值。 VARCHAR是CHARACTER VARYING一个缩写。

 

复合类型

TINYBLOB

 YEAR       1         1901/2155               YYYY       年份值 

 

FLOAT          4字节   (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度浮点数值 

  ENUM 类型因为只允许在集合中取得一个值,有点类似于单选项。在处理相互排拆的数据时容易让人理解,比如人类的性别。ENUM 类型字段可以从集合中取得一个值或使用 null 值,

如果我们对 TIMESTAMP 类型的字段没有明确赋值,或是被赋与了 null 值。MySQL 会自动使用系统当前的日期和时间来填充它。

    LONGTEXT     0-4 294 967 295字节 极大文本数据

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
一个很小的整数。有符号的范围是-128到127,无符号的范围是0到255。

因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度,所以在不能确定字段需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。

通过对每种数据类型的用途,物理存储,表示范围等有一个概要的了解。这样在面对具体应用时,就可以根据相应的特来来选择合适的数据类型,使得我们能够争取在满足应用的基础上,

    BINARY(M)    M                 允许长度0-M个字节的定长字节符串

UNSIGNED 修饰符规定字段只保存正值。因为不需要保存数字的正、负符号,可以在储时节约一个“位”的空间。从而增大这个字段可以存储的值的范围。

  需要注意的是,没有冒号分隔符的 TIME 类型值,将会被 MySQL 理解为持续的时间,而不是时间戳。

 在处理日期和时间类型的值时,MySQL 带有 5 个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型,和混合日期、时间类型。

 一个 SET 类型最多可以包含 64 项元素。在 SET 元素中值被存储为一个分离的“位”序列,这些“位”表示与它相对应的元素。“位”是创建有序元素集合的一种简单而有效的方式。

本文由金沙官网线上发布于数据库,转载请注明出处:Mysql支持的数据类型(列类型总结)金沙官网线上:

您可能还会对下面的文章感兴趣: