【MySQL--04】数据类型

1.数据类型

1.1数据类型分类

图1.1-1

1.2数值类型

图1.2-1

1.2.1tinyint类型

数值越界测试：

mysql> create table t1 -> (id tinyint);
Query OK, 0 rows affected (0.17 sec)mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)mysql> insert into t1 values (1);
Query OK, 1 row affected (0.03 sec)mysql> insert into t1 values (128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t1 values (127);
Query OK, 1 row affected (0.05 sec)mysql> insert into t1 values (-127);
Query OK, 1 row affected (0.02 sec)mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.03 sec)mysql> insert into t1 values (-129);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|  127 |
| -127 |
| -128 |
+------+
4 rows in set (0.00 sec)

说明：

• 在MySQL中，整形可以指定具有符号的和无符号的，默认是有符号的。

• 可以通过UNSIGNED来说明某个字段是无符号的。

• 无符号案例

  mysql> create table t2(id tinyint unsigned);
  Query OK, 0 rows affected (0.26 sec)mysql> insert into t2 values (0);
  Query OK, 1 row affected (0.03 sec)mysql> insert into t2 values (-1);
  ERROR 1264 (22003): Out of range value for column 'id' at row 1
  mysql> insert into t2 values (1);
  Query OK, 1 row affected (0.03 sec)mysql> insert into t2 values (255);
  Query OK, 1 row affected (0.04 sec)mysql> insert into t2 values (256);
  ERROR 1264 (22003): Out of range value for column 'id' at row 1
  mysql> select * from t1;
  +------+
  | id   |
  +------+
  |    1 |
  |  127 |
  | -127 |
  | -128 |
  +------+
  4 rows in set (0.01 sec)mysql> desc t1;
  +-------+------------+------+-----+---------+-------+
  | Field | Type       | Null | Key | Default | Extra |
  +-------+------------+------+-----+---------+-------+
  | id    | tinyint(4) | YES  |     | NULL    |       |
  +-------+------------+------+-----+---------+-------+
  1 row in set (0.02 sec)
  

• 其他类型自己推导

注意：尽量不适用unsigned，对于int类型可能存在放不下的数据，int unsigned同样可以存放不下，与其如此，还不如设计时，讲int类型提升为bigint类型。

1.2.2bit类型

基本语法：

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

示例：

mysql> create table t3(id int,a bit(8));
Query OK, 0 rows affected (0.21 sec)mysql> desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| a     | bit(8)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t3 values (1,10);
Query OK, 1 row affected (0.03 sec)mysql> select * from t3;
+------+------+
| id   | a    |
+------+------+
|    1 | |
+------+------+
1 row in set (0.00 sec)

发现很奇怪的现象，a的数据10没有出现

bit使用的注意事项：

• bit字段在显示时，是按照ASCII码对应的值显示。

mysql> insert into t3 values(65,65);
Query OK, 1 row affected (0.02 sec)mysql> select * from t3;
+------+------+
| id   | a    |
+------+------+
|    1 | |
|   65 | A    |
+------+------+
2 rows in set (0.00 sec)

• 如果我们有这样的值，只存放0或者1，这时候可以定义为bit(1)。这样可以节省空间

mysql> create table t4(-> gender bit(1));
Query OK, 0 rows affected (0.21 sec)mysql> insert into t4 values (1);
Query OK, 1 row affected (0.02 sec)mysql> insert into t4 values (0);
Query OK, 1 row affected (0.03 sec)mysql> insert into t4 values (2); --当插入2时,已经越界了
ERROR 1406 (22001): Data too long for column 'gender' at row 1

1.2.3小数类型

1.2.3.1 float

语法：

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

示例：

小数:float(4,2) :表示一共最多可以有4位长度,其中小数点后两位,意味着小数点前最多2位,即数据范围为 -99.99~99.99,MySQL在保存值时会进行四舍五入.

mysql> create table t5(num float(4,2));
Query OK, 0 rows affected (0.26 sec)mysql> insert into t5 values (-99.99);
Query OK, 1 row affected (0.03 sec)mysql> insert into t5 values (99.99);
Query OK, 1 row affected (0.04 sec)mysql> insert into t5 values (99.991);
Query OK, 1 row affected (0.04 sec)mysql> select * from t5;
+--------+
| num    |
+--------+
| -99.99 |
|  99.99 |
|  99.99 |
+--------+
3 rows in set (0.01 sec)mysql> insert into t5 values (99.995);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t5 values (-99.994);
Query OK, 1 row affected (0.04 sec)mysql> select * from t5;
+--------+
| num    |
+--------+
| -99.99 |
|  99.99 |
|  99.99 |
| -99.99 |
+--------+
4 rows in set (0.01 sec)

注意：当我们的float(4,2)如果是一个有符号的,则表示范围是(-99.99,99.99),如果是无符号的,范围会变成(0,99.99)

示例：

mysql> create table t6(num float(4,2) unsigned);
Query OK, 0 rows affected (0.06 sec)mysql> insert into t6 values(0);
Query OK, 1 row affected (0.04 sec)mysql> insert into t6 values(99.99);
Query OK, 1 row affected (0.03 sec)mysql> insert into t6 values(-0);
Query OK, 1 row affected (0.03 sec)mysql> insert into t6 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from t6;
+-------+
| num   |
+-------+
|  0.00 |
| 99.99 |
|  0.00 |
+-------+
3 rows in set (0.00 sec)

1.2.3.2 decimal

语法：

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

• decimal(5,2)表示范围是-999.99~999.99

• decimal(5,2)unsigned表示的范围0~999.99

• decimal和float很像,但是有区别：

  • float和decimal表示的精度不一样 -- decimal精度更高更准确

    mysql> create table t7(num1 float(10,8),num2 decimal(10,8));
    Query OK, 0 rows affected (0.56 sec)mysql> desc t7;
    +-------+---------------+------+-----+---------+-------+
    | Field | Type          | Null | Key | Default | Extra |
    +-------+---------------+------+-----+---------+-------+
    | num1  | float(10,8)   | YES  |     | NULL    |       |
    | num2  | decimal(10,8) | YES  |     | NULL    |       |
    +-------+---------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)mysql> insert into t7 values(23.12345612,23.12345612);
    Query OK, 1 row affected (0.04 sec)mysql> select * from t7; -- 发现decimal的精度更准确，因此我们希望数据高精度选择decimal
    +-------------+-------------+
    | num1        | num2        |
    +-------------+-------------+
    | 23.12345695 | 23.12345612 |
    +-------------+-------------+
    1 row in set (0.00 sec)
    

    说明：float表示的精度大约是7位

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

建议：如果希望小数的精度高，推荐使用decimal。

1.3字符串类型

1.3.1 char

语法：

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

示例：

mysql> create table t8 (name char(2));
Query OK, 0 rows affected (0.19 sec)mysql> insert into t8 values('ab');
Query OK, 1 row affected (0.04 sec)mysql> insert into t8 values('语言');
Query OK, 1 row affected (0.03 sec)mysql> select * from t8;
+--------+
| name   |
+--------+
| ab     |
| 语言   |
+--------+
2 rows in set (0.00 sec)

说明：char(2)表示可以存放两个字符，可以是字母或者汉字，但是不能超过2个。L的最大值只能是255

mysql> create table t9 (name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

1.3.2 varchar

语法：

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

示例：

mysql> create table tt1(name varchar(6)); -- 表示可以存放6个字符
Query OK, 0 rows affected (0.23 sec)mysql> desc tt1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(6) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)mysql> insert into tt1 values ('hello');
Query OK, 1 row affected (0.04 sec)mysql> insert into tt1 values ('我爱你,祖国');
Query OK, 1 row affected (0.02 sec)mysql> insert into tt1 values ('我很爱你,祖国');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from tt1;
+------------------+
| name             |
+------------------+
| hello            |
| 我爱你,祖国      |
+------------------+
2 rows in set (0.00 sec)

说明：

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

• varchar长度可以指定为0到65535之间的值，但是有1-3个字节用于记录数据大小，所以说有效字节数是65535。
• 当我们的表的编码是utf8时,varchar(n)的参数n的最大值为65535/3=21844(因为utf中,一个字符占3个字节)，如果编码是gbk,varchar(n)的参数n的最大值为65536/2=32766(因为gbk一个字符占2个字节)

mysql> create table tt2(name varchar(21845))charset=utf8; -- 验证utf8确实不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table tt2(name varchar(21844))charset=utf8;
Query OK, 0 rows affected (0.21 sec)mysql> create table tt3(name varchar(32767))charset=gbk; -- 验证gbk确实不能超过32766
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table tt3(name varchar(32766))charset=gbk;
Query OK, 0 rows affected (0.24 sec)

1.3.3char和varchar的比较

实际存储	char(4)	varcahr(4)	char占用字节	varchar占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
Abcde	X	X	数据超过长度	数据超过长度

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就是用定长char，比如：身份证，手机号等
• 如果数据长度有变化，就是用变长varchar，比如：名字，地址，但是你要保证最长的能存进去
• 定长的磁盘空间比较浪费，但是效率高
• 变长的磁盘空间比较节省，但是效率低
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

1.4日期和时间类型

常用的日期有如下三个：

• data ：日期 'yyyy-mm-dd'，占三个字节
• datetime : 时间日期格式'yyyy-mm-dd HH:ii:ss'表示范围从1000到9999，占用8字节
• timestamp : 时间辍(格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数) 格式: yyyy-mm-dd HH:ii:ss 和datetime完全一致，占用四字节。

示例：

mysql> create table tt3(t1 date,t2 datetime,d3 timestamp);
ERROR 1050 (42S01): Table 'tt3' already exists
mysql> create table tt4(t1 date,t2 datetime,d3 timestamp);
Query OK, 0 rows affected (0.17 sec)mysql> insert into tt4 (t1,t2) values('2023-04-11','1999-7-1 8:0:0');
Query OK, 1 row affected (0.03 sec)mysql> select * from tt4;
+------------+---------------------+---------------------+
| t1         | t2                  | d3                  |
+------------+---------------------+---------------------+
| 2023-04-11 | 1999-07-01 08:00:00 | 2023-04-11 20:58:24 |
+------------+---------------------+---------------------+
1 row in set (0.01 sec)

我们可以看到，添加数据时，时间戳自动补上当前时间。这是因为d3有默认值。我们可以查看一下tt4的表结构.

mysql> desc tt4;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t1    | date      | YES  |     | NULL              |                             |
| t2    | datetime  | YES  |     | NULL              |                             |
| d3    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)

我们如果更新t1的时间，表中又会有什么变化

mysql> select * from tt4;
+------------+---------------------+---------------------+
| t1         | t2                  | d3                  |
+------------+---------------------+---------------------+
| 2023-04-11 | 1999-07-01 08:00:00 | 2023-04-11 20:58:24 |
+------------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> update tt4 set t1='2000-1-1';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from tt4;
+------------+---------------------+---------------------+
| t1         | t2                  | d3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 1999-07-01 08:00:00 | 2023-04-11 21:00:44 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

我们发现，更新数据时，时间戳会更新成当前时间。

1.5 enum和set

1.5.1 enum

语法：

enum：枚举，单选类型

enum('选项1','选项2','选项3'.....);

该设定值只是提供了若干个选项的值，最终一个单元格中，实际值存储了其中一个值，而是处于效率考虑，这些值实际存储的是 数字，因为这些选项的每个选项值一次对应如下数字：1,2,3,.....最多65535个，当我们添加枚举值时，也可以添加对应的数字编号。

1.5.2 set

语法：

set：集合，多选类型

set('选项值1','选项值2','选项值3',......);

该设定值只是提供了若干个选项的值，最终一个单元格中，设计可以存储了其中任意多个值，而且处于效率考虑，这些值实际存储的是 位图数字，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32.....最多64个

示例：set('选项1','选项2','选项3','选项4','选项5');

选项1	选项2	选项3	选项4	选项5
00001	00010	00100	01000	10000

因此如果你要同时选择选项1和选项2可以输入3 因为00001 + 00010 = 00011对应的10进制数字3

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读

1.5.3 示例

示例：

有一个调查表votes，需要调查人的喜好，比如(登山,游泳,篮球,武术)中去选择(可以多选)，性别(男,女)[单选]

mysql> create table votes( 
name varchar(20), 
gender enum('男','女'), 
hobby set('登山','游泳','篮球','武术')
);
Query OK, 0 rows affected (0.21 sec)
mysql> desc votes;
+--------+------------------------------------------+------+-----+---------+-------+
| Field  | Type                                     | Null | Key | Default | Extra |
+--------+------------------------------------------+------+-----+---------+-------+
| name   | varchar(20)                              | YES  |     | NULL    |       |
| gender | enum('男','女')                          | YES  |     | NULL    |       |
| hobby  | set('登山','游泳','篮球','武术')           | YES  |     | NULL    |       |
+--------+------------------------------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

插入数据：

mysql> insert into votes values('张三','男','登山');
Query OK, 1 row affected (0.05 sec)mysql> insert into votes values('李四','1','登山,篮球');
Query OK, 1 row affected (0.02 sec)mysql> insert into votes values('王五','2','7');
Query OK, 1 row affected (0.02 sec)mysql> insert into votes values('赵六','2','15');
Query OK, 1 row affected (0.04 sec)mysql> select * from votes;
+--------+--------+-----------------------------+
| name   | gender | hobby                       |
+--------+--------+-----------------------------+
| 张三   | 男     | 登山                         |
| 李四   | 男     | 登山,篮球                    |
| 王五   | 女     | 登山,游泳,篮球               |
| 赵六   | 女     | 登山,游泳,篮球,武术          |
+--------+--------+-----------------------------+
4 rows in set (0.00 sec)

如表中数据，想要查找所有喜欢登山的人：

mysql> select * from votes;
+--------+--------+-----------------------------+
| name   | gender | hobby                       |
+--------+--------+-----------------------------+
| 张三   | 男     | 登山                        |
| 李四   | 男     | 登山,篮球                   |
| 王五   | 女     | 登山,游泳,篮球              |
| 赵六   | 女     | 登山,游泳,篮球,武术         |
+--------+--------+-----------------------------+
4 rows in set (0.00 sec)mysql> select * from votes where hobby = '登山';
+--------+--------+--------+
| name   | gender | 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 find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)mysql> select find_in_set('d','a,b,c');
+--------------------------+
| find_in_set('d','a,b,c') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

因此查询爱好登山的人

mysql> select * from votes where find_in_set('登山',hobby);
+--------+--------+-----------------------------+
| name   | gender | hobby                       |
+--------+--------+-----------------------------+
| 张三   | 男     | 登山                        |
| 李四   | 男     | 登山,篮球                   |
| 王五   | 女     | 登山,游泳,篮球              |
| 赵六   | 女     | 登山,游泳,篮球,武术         |
+--------+--------+-----------------------------+
4 rows in set (0.00 sec)

查询同时爱好登山和游泳的人

mysql> select * from votes where find_in_set('登山',hobby) and find_in_set('游泳',hobby);
+--------+--------+-----------------------------+
| name   | gender | hobby                       |
+--------+--------+-----------------------------+
| 王五   | 女     | 登山,游泳,篮球              |
| 赵六   | 女     | 登山,游泳,篮球,武术         |
+--------+--------+-----------------------------+
2 rows in set (0.00 sec)

（本篇完）