Mysql数据类型

一：数据类型分类

二：数值类型分类

mysql> create table dk(
    -> num tinyint);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into dk values(-128);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dk values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

当插入范围之外的值时，mysql会报错，而后面我们也会学习到数据类型实际上就是一种约束，保证了数据的可靠性。

此外数值类型可以使用unsigned说明某个字段是无符号的。

mysql> create table dk2( num tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into dk2 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into dk2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into dk2 values(0);
Query OK, 1 row affected (0.00 sec)

可以看见只能插入大于等于0的数字。

三：bit类型

bit [(M)] : 位字段类型。 M 表示每个值二进制的位数，范围从 1 到 64 。如果 M 被忽略，默认为 1 。

 根据位图所学，比特位指定了位数，那么其表示的最大值是2^M-1。如：

mysql> create table bite(
    -> id int,
    -> ^C
mysql> create table bite(
    -> id int(4),
    -> num bit(4));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into bite values(10,20);
ERROR 1406 (22001): Data too long for column 'num' at row 1
mysql> insert into bite values(10,15);
Query OK, 1 row affected (0.00 sec)

4位的位图，最大只能表示15，所以插入20是会报错的。

3.1：位图显示

mysql> select* from bite;
+------+------+
| id   | num  |
+------+------+
|   10 |     |
+------+------+

可以看到插入的15并没有显示出来。这是因为bit字段在显示时，是按照ASCII码对应的值显示。

mysql> select hex(num) from bite;
+----------+
| hex(num) |
+----------+
| F        |
+----------+
1 row in set (0.00 sec)

我们把对应的插入数据采用16进制方式打印出来。

mysql> create table bite(
    -> id int(8),
    -> num bit(8));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into bite values(10000000,97);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into bite values(10000001,'a');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from bite;
+----------+------+
| id       | num  |
+----------+------+
| 10000000 | a    |
| 10000001 | a    |
+----------+------+
2 rows in set (0.00 sec)

因为a的ASCII码是97所以也能直接插入该字母，可以看到97对应的是字母a。

此外int数值类型后面跟的位数表示的是这个数10进制的位数，而bit位图是二进制的位数。

如果位图只需要插入0或者1，那我们可以直接开一个bit（1）即可。

四：小数类型

4.1：float

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节 

 

mysql> create table t(num float(4,2));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into t values(10.00);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t values(99.93);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values(99.99);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values(100.00);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t values(-99.99);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values(-100.0);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t values(-100.0);

 4位有效长度，2位小数，那么表示的范围就是-99.99----99.99。同样的float也可以采用unsigned。

mysql> select * from t;
+--------+
| num    |
+--------+
|  10.00 |
|  99.93 |
|  99.99 |
| -99.99 |
+--------+
4 rows in set (0.00 sec)
 
mysql> insert into t values(-99.985);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t;
+--------+
| num    |
+--------+
|  10.00 |
|  99.93 |
|  99.99 |
| -99.99 |
| -99.98 |
+--------+

当我们插入范围内的一个有三位小数的数字时，mysql会进行四舍五入。

4.2：decimal

 decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

decimal和float很像，但是有区别：

decimal的精度更高。

同时对两表插入23.12345678

得到的结果如下：

mysql> select * from t2;
+-------------+
| num         |
+-------------+
| 23.12345695 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from t3;
+-------------+
| num         |
+-------------+
| 23.12345678 |
+-------------+

 说明float的精度大致是在7位（根据不同的mysql版本和系统有所不同可能）

• decimal 整数最大位数 m 为 65 。支持小数最大位数 d 是 30 。如果 d 被省略，默认为 0. 如果 m 被省略，默认是 10 。

五：字符串类型

5.1：char

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255 

注意：单位是字符

mysql> create table t(
    -> name char(2));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into t values('中国');
Query OK, 1 row affected (0.00 sec)

 如果在c++中中国会被识别成4个字符，但是mysql中的char单位就是字符，中国就是2个字符。

5.2：varchar

varchar(L): 可变长度字符串， L 表示字符长度，最大长度 65535 个字节

 注意：L表示的是字符长度。

mysql> create table t2(
    -> name varchar(6));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into t2 values('我爱你,中国');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t2;
+------------------+
| name             |
+------------------+
| 我爱你,中国      |
+------------------+

mysql> create table t2( name varchar(65535));
ERROR 1074 (42000): Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead

 当我想创建65535字节大小的时候会报错，为什么呢，因为len代表的是字符，而len这里是有要求的。

关于 varchar(len),len 到底是多大，这个 len 值，和表的编码密切相关：

varchar 长度可以指定为 0 到 65535 之间的值，但是有 1 - 3 个字节用于记录数据大小，所以说有效字节数是

65532 。

当我们的表的编码是 utf8 时， varchar(n) 的参数 n 最大值是 65532/3=21844[ 因为 utf 中，一个字符占用 3 个字

节 ] ，如果编码是 gbk ， varchar(n) 的参数 n 最大是 65532/2=32766 （因为 gbk 中，一个字符占用 2 字节）。

 5.3：char与varchar比较

 

六：日期和时间类型

date :日期 'yyyy-mm-dd' ，占用三字节

datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节

timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节  

 

mysql> create table t(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp);
 
insert into t values('2023-11-4','2023-11-4 20:37:33');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `t1` date DEFAULT NULL,
  `t2` datetime DEFAULT NULL,
  `t3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

第三个字段timestamp通过我们查看表创建的时候，可以看到当我们插入t3字段所在行的数据的时候，t3这里会自动填充上当前系统的时间。也就是说第三行不需要我们插入，所以我们插入的时候如果省略t3 直接后面使用values会报错，因此，需要指定t1和t2字段插入。效果如下：

mysql> insert into t(t1,t2) values('2023-11-4','2023-11-4 20:37:33');
Query OK, 1 row affected (0.00 sec)
 
 
mysql> select * from t;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2023-11-04 | 2023-11-04 20:37:33 | 2023-11-04 20:39:47 |
+------------+---------------------+---------------------+

 

注意：我们插入的时候月份和日期前面就算不加0，mysql也会自动给我们添加上。

七：enum和set

7.1：enum 

enum：枚举，“多选一”的单选类型

enum（'选项一','选项二'） 

mysql> desc t;
+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| name   | varchar(4)        | YES  |     | NULL    |       |
| gender | enum('男','女')   | YES  |     | NULL    |       |
+--------+-------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into t values('张三','男');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('张四','女');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('张五','人');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

插入的时候，只要插入enum选项中有的就行，如果是插入的不在enum中，则报错，此外enum也可以通过数字插入：

mysql> insert into t values('张五',1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('张五',2);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
+--------+--------+
| name   | gender |
+--------+--------+
| 张三   | 男     |
| 张四   | 女     |
| 张五   | 男     |
| 张五   | 女     |
+--------+--------+
4 rows in set (0.00 sec)
 
mysql> insert into t values('张五',0);
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

可以看到enum中通过数字下标插入默认是从1开始的。

7.2：set

set：集合，“单选多选”都可以。

set（'选项一','选项二'） 

mysql> desc t;
+-------+---------------------------------------------------------+------+-----+---------+-------+
| Field | Type                                                    | Null | Key | Default | Extra |
+-------+---------------------------------------------------------+------+-----+---------+-------+
| name  | varchar(4)                                              | YES  |     | NULL    |       |
| hobby | set('写代码','玩游戏','吃饭','睡觉','内卷')             | YES  |     | NULL    |       |
+-------+---------------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> insert into t values('张三','玩游戏');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('李四','玩游戏,吃饭');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('赵六','玩游戏,内卷');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('陈七','玩游戏,写代码,吃饭,睡觉,内卷');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
+--------+------------------------------------------+
| name   | hobby                                    |
+--------+------------------------------------------+
| 张三   | 玩游戏                                   |
| 李四   | 玩游戏,吃饭                              |
| 赵六   | 玩游戏,内卷                              |
| 陈七   | 写代码,玩游戏,吃饭,睡觉,内卷             |
+--------+------------------------------------------+
4 rows in set (0.00 sec)

 那么我们可不可以和enum一样直接用数字插入呢？

mysql> insert into t values('赵日天',1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('赵日地',2);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t values('秦始皇',3);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
+-----------+------------------------------------------+
| name      | hobby                                    |
+-----------+------------------------------------------+
| 张三      | 玩游戏                                   |
| 李四      | 玩游戏,吃饭                              |
| 赵六      | 玩游戏,内卷                              |
| 陈七      | 写代码,玩游戏,吃饭,睡觉,内卷             |
| 赵日天    | 写代码                                   |
| 赵日地    | 玩游戏                                   |
| 秦始皇    | 写代码,玩游戏                            |
+-----------+------------------------------------------+
7 rows in set (0.00 sec)

 可以看到秦始皇的hobby是2个。

这是因为set采用数字插入的时候，采用的位图的方案，1代表001，2是010,3是011，所以秦始皇的写代码和玩游戏爱好都有，这也体现出了set数字插入的时候位图中的下标也是从1开始。

 假如我们要查询喜欢写代码的人有哪些该如何做呢？

mysql> select * from t;
+-----------+------------------------------------------+
| name      | hobby                                    |
+-----------+------------------------------------------+
| 张三      | 玩游戏                                   |
| 李四      | 玩游戏,吃饭                              |
| 赵六      | 玩游戏,内卷                              |
| 陈七      | 写代码,玩游戏,吃饭,睡觉,内卷             |
| 赵日天    | 写代码                                   |
| 赵日地    | 玩游戏                                   |
| 秦始皇    | 写代码,玩游戏                            |
+-----------+------------------------------------------+
7 rows in set (0.00 sec)
 
mysql> select * from t where hobby='写代码';
+-----------+-----------+
| name      | hobby     |
+-----------+-----------+
| 赵日天    | 写代码    |
+-----------+-----------+
1 row in set (0.00 sec)

这样查询显然不对，因为这是一种绝对查询，只会查询出爱好只有写代码的。

所以我们需要运用到一个叫find_in_set的函数。

 

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回 0 ； str_list 用逗号分

隔的字符串。

 

mysql> select * from t where find_in_set('写代码',hobby);
+-----------+------------------------------------------+
| name      | hobby                                    |
+-----------+------------------------------------------+
| 陈七      | 写代码,玩游戏,吃饭,睡觉,内卷             |
| 赵日天    | 写代码                                   |
| 秦始皇    | 写代码,玩游戏                            |
+-----------+------------------------------------------+
3 rows in set (0.00 sec)

这样查询就对了，那么数字呢？

mysql> select * from t where find_in_set(1,hobby);
Empty set (0.00 sec)
 
mysql> select * from t where find_in_set(2,hobby);
Empty set (0.00 sec)

可见再通过位图数字查询是不可行了。