MySQL数据类型详解-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_20176495/article/details/79560750

MySQL支持的数据类型

数值类型

1、整数类型

所有的整数类型都有一个可选属性unsigned
auto_increment：产生唯一标识或顺序值
1. 在插入null到一个auto_increment列时，MySQL插入一个比该列中最大值大1的值。
2. 一个表最多只能有一个auto_increment列。
3. 任何一个auto_increment列应该定义为not null，并且定义为primary key或者定义为unique键。

2、小数类型

浮点数：float和double
定点数：decimal

定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
“(M,D)”表示该值一共显示M位数字，其中D位位于小数点后面，M和D又称为精度和标度。
MySQL保存值时进行四舍五入。

mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1   | float         | YES  |     | NULL    |       |
| id2   | double        | YES  |     | NULL    |       |
| id3   | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+-------+-------+------+
| id1   | id2   | id3  |
+-------+-------+------+
| 1.234 | 1.234 |    1 |
+-------+-------+------+
2 rows in set (0.00 sec)

浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；
定点数如果不写精度和标度，则会按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

3、位类型

存放位字段值，bit(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位。
对于位字段，直接使用select命令看不到结果，可以用bin()（显示二进制格式）或者hex()（显示为十六进制格式）函数进行读取。

mysql> create table t2(id bit);
Query OK, 0 rows affected (0.03 sec)

mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.01 sec)

可以发现，直接使用select *的结果为null。

mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.01 sec)

注意：数据插入bit类型字段时，首先转换为二进制，如果位数允许，将成功插入；如果位数小于实际定义的位数，则插入失败。

mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.01 sec)

mysql> insert into t2 values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1

将id定义修改为bit(2)后，重新插入，插入成功：

mysql> alter table t2 modify id bit(2);
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(2) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t2 values(2);
Query OK, 1 row affected (0.03 sec)

mysql> select bin(id),hex(id) from t2;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
| 10      | 2       |
+---------+---------+
2 rows in set (0.00 sec)

日期时间类型

MySQL日期时间类型

可以采用不同的格式将日期“2018-03-15 19:10:10”插入到datetime列中：

‘2018-03-15 19:10:10’
‘2018/03/15 19+10+10’
‘20180315191010’
20180315191010

最终select查询出来的显示格式均是2018-03-15 19:10:10

下面讲下最常用的3中日期类型：date、time、datetime

mysql> create table t(
    -> d date,
    -> t time,
    -> dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

用now()插入当前时间：

mysql> insert into t values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-03-15 | 19:21:57 | 2018-03-15 19:21:57 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

timestamp和datetime有所不同，下面说说timestamp的一些特性：

首先添加一个类型为timestamp的数据列：

mysql> alter table t add column ts timestamp;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | YES  |     | NULL              |                             |
| t     | time      | YES  |     | NULL              |                             |
| dt    | datetime  | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

系统给ts列自动创建了默认值CURRENT_TIMESTAMP（系统日期）：

mysql> select * from t;
+------------+----------+---------------------+---------------------+
| d          | t        | dt                  | ts                  |
+------------+----------+---------------------+---------------------+
| 2018-03-15 | 19:21:57 | 2018-03-15 19:21:57 | 2018-03-15 19:24:01 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into t(ts) values(null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------------+----------+---------------------+---------------------+
| d          | t        | dt                  | ts                  |
+------------+----------+---------------------+---------------------+
| 2018-03-15 | 19:21:57 | 2018-03-15 19:21:57 | 2018-03-15 19:24:01 |
| NULL       | NULL     | NULL                | 2018-03-15 19:26:00 |
+------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

如果再添加一个类型为timestamp的数据列，还会有默认值吗？

mysql> alter table t add column ts2 timestamp;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------------+----------+---------------------+---------------------+---------------------+
| d          | t        | dt                  | ts                  | ts2                 |
+------------+----------+---------------------+---------------------+---------------------+
| 2018-03-15 | 19:21:57 | 2018-03-15 19:21:57 | 2018-03-15 19:24:01 | 0000-00-00 00:00:00 |
| NULL       | NULL     | NULL                | 2018-03-15 19:26:00 | 0000-00-00 00:00:00 |
+------------+----------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> show create table t \G;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `d` date DEFAULT NULL,
  `t` time DEFAULT NULL,
  `dt` datetime DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

注意：MySQL只给表中第一个timestamp字段设置默认值为系统日期，如果有第二个timestamp类型，则默认值设置为0值

timestamp还有一个重要的特性，就是和时区相关。插入日期时，先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。
就该特性，将timestamp和datetime进行比较：

mysql> create table t3(
    -> id1 timestamp,
    -> id2 datetime
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t3 \G;
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

查看当前时区：
这里的时区值为“SYSTEM”，这个默认值是和主机的时区值一致的，即东八区（+8:00）；

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.01 sec)

mysql> insert into t3 values(now(),now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-03-15 19:39:46 | 2018-03-15 19:39:46 |
+---------------------+---------------------+
1 row in set (0.00 sec)

下面将时区改为东九区：

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t3;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2018-03-15 20:39:46 | 2018-03-15 19:39:46 |
+---------------------+---------------------+
1 row in set (0.00 sec)

MySQL提供了FROM_UNIXTIME()函数将时间戳转为日期，也提供了UNIX_TIMESTAMP()函数将日期转为时间戳。

timestamp和datetime非常相似，区别主要有以下几点：

timestamp支持的时间范围较小；
表中的第一个timestamp列自动设置为系统时间；当插入的时间超出范围时，使用“0000-00-00 00:00：00”进行填补；
timestamp的插入和查询受当地时区的影响，更反映出实际的日期；
timestamp的属性受MySQL版本和服务器SQLMode的影响很大。

字符串类型

char 和 varchar

区别：在检索的时候，char列删除了尾部的空格，而varchar则保留这些空格。
char 表示定长，长度固定，varchar表示变长，即长度可变。当所插入的字符串超出它们的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入。如果插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。
varchar怎么知道所存储字符串的长度呢？实际上，对于varchar字段来说，需要使用一个（如果字符串长度小于255）或两个字节（长度大于255）来存储字符串的长度。因为他需要有一个prefix来表示他具体bytes数是多少（因为varchar是变长的，没有这个长度值他不知道如何读取数据）。

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

mysql> select length(v),length(c) from vc;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> select concat(v,'+'),concat(c,'+') from vc;
+---------------+---------------+
| concat(v,'+') | concat(c,'+') |
+---------------+---------------+
| ab  +         | ab+           |
+---------------+---------------+
2 rows in set (0.00 sec)

char和varchar的选择？
VARCHAR适合的使用场景有：字符串的最大长度比平均长度大很多；列的更新较少；使用了UTF8这样的复杂字符集使得长度不确定。CHAR适合的使用场景有：所有值长度接近如密码MD5值、Y或N值等等。

binary 和 varbinary

mysql> create table b(c binary(3));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into b values('a');
Query OK, 1 row affected (0.01 sec)

分别用以下几种模式来查看c列的内容：

mysql> select *,hex(c),c='a',c='a\0',c='a\0\0' from b;
+------+--------+-------+---------+-----------+
| c    | hex(c) | c='a' | c='a\0' | c='a\0\0' |
+------+--------+-------+---------+-----------+
| a    | 610000 |     0 |       0 |         1 |
+------+--------+-------+---------+-----------+
1 row in set (0.00 sec)

当保存binary值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义的长度。

enum类型

它的值范围需要在创建表时通过枚举方式显示指定。

mysql> create table e(gender enum('M','F'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc e;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| gender | enum('M','F') | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into e values('M'),('2'),('f'),(null);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from e;
+--------+
| gender |
+--------+
| M      |
| F      |
| F      |
| NULL   |
+--------+
4 rows in set (0.00 sec)

可以看出，enum类型是忽略大小写的，在存储“M”、“f”时将它们都转成了大写，“1”代表了第一个对象“M”，“2”代表了第二个对象“F”。

mysql> insert into e values('a');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

当插入不在enum范围内的值时，抛出错误。

enum类型只允许从值集合中选取单个值，而不能一次取出多个值。

set类型

与enum最主要的区别就是，set类型一次可以取出多个成员。

mysql> create table s(
    -> col set('a','b','c','d')
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc s;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col   | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into s values('a','b');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into s values('a,b'),('a,d,a');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from s;
+------+
| col  |
+------+
| a,b  |
| a,d  |
+------+
2 rows in set (0.00 sec)

mysql> insert into s values('a,f');
ERROR 1265 (01000): Data truncated for column 'col' at row 1