【MySQL】数据库基础（数据类型）

一、数据类型分类

二、数值类型

2.1 tinyint类型

2.2 bit类型

2.3 float类型

2.4 decimal类型

三、字符串类型

3.1 char类型

3.2 varchar类型

3.3 char和varchar比较

四、时间和日期类型

五、枚举和集合类型

5.1 enum类型

5.2 set类型

一、数据类型分类

二、数值类型

注意：

在C/C++中，数据越界会发生截断，不一定会报错，而MySQL会对插入的数据进行检查，如果越界MySQL会直接拦截，不允许该操作，这也保证了MySQL中的数据一定是合法的。
MySQL表中建立属性列，名称在前，属性在后。

2.1 tinyint类型

上面的几种类型用法类似，会用tinyint类型也就会用其它类型了。

数值越界测试：

mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.03 sec)mysql> insert into tt1 values(1);
Query OK, 1 row affected (0.01 sec)mysql> insert into tt1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from tt1;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

说明:

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
可以通过UNSIGNED来说明某个字段是无符号的

无符号示例：

mysql> create table tt2(num tinyint unsigned);mysql> insert into tt2 values(-1); -- 无符号，范围是： 0 - 255
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tt2 values(255);
Query OK, 1 row affected (0.02 sec)mysql> select * from tt2;
+------+
| num |
+------+
| 255 |
+------+
1 row in set (0.00 sec)

注意：

是否使用unsigned要根据具体场景确定。
如果unsigned不是必要，则尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

2.2 bit类型

在MySQL中，bit/BIT类型用于存储位字段。bit类型的字段可以存储1到64位的位值。将一个值插入到bit类型的列中时，MySQL会自动将该值转换为二进制格式，并使用十六进制表示法显示。

用法：

列名 bit[(M)]

说明：

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

示例：

mysql> create table tt4 ( > id int, > a bit(8));
Query OK, 0 rows affected (0.01 sec)mysql> insert into tt4 values(10, 10);
Query OK, 1 row affected (0.01 sec)mysql> select * from tt4;
+------+------------+
| id   | a          |
+------+------------+
|   10 | 0x0A       |
+------+------------+
1 row in set (0.00 sec)

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

mysql> create table tt5(gender bit(1));
Query OK, 0 rows affected (0.05 sec)mysql> insert into tt5 values(0);
Query OK, 1 row affected (0.01 sec)mysql> insert into tt5 values(1);
Query OK, 1 row affected (0.00 sec)mysql> insert into tt5 values(2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1

2.3 float类型

float类型的用法与double类型的用法类似，在此只介绍float类型。

用法：

列名 float[(m, d)] [unsigned]

说明：

m指定显示长度，即浮点数总长度。
d指定小数位数。
占用空间4个字节。
float类型默认可以存储大约7位有效数字，但可以指定精度
float类型适用于需要一定精度但不是特别高的数值存储，如果数据长度超过指定长度或位数，数据可能会不精确或四舍五入。

示例：

mysql> create table tt6(id int, salary float(4,2));
Query OK, 0 rows affected, 1 warning (0.03 sec)mysql> insert into tt6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)mysql> insert into tt6 values(101, -99.991);
Query OK, 1 row affected (0.00 sec)mysql> insert into tt6 values(101, -99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into tt6 values(102, -99.985);
Query OK, 1 row affected (0.01 sec)mysql> insert into tt6 values(103, -99.989);
Query OK, 1 row affected (0.01 sec)mysql> select * from tt6;
+------+--------+
| id   | salary |
+------+--------+
|  100 | -99.99 |
|  101 | -99.99 |
|  102 | -99.98 |
|  103 | -99.99 |
+------+--------+
4 rows in set (0.00 sec)

注意：

-99.995时，由于其超出FLOAT(4,2)类型的精度范围，MySQL无法接受这个值。
-99.985虽然被四舍五入，但结果还是-99.98，这是因为其仍然在有效数字范围内，而且小数的保存规则一般是乘二取整，存储结构造成其不是-99.99，这也说明float的精度不是很高。
如果需要更高的精度，可以用double或者decimal。

2.4 decimal类型

用法：

列名 decimal(m, d) [unsigned]

说明：

定点数m指定长度
d表示小数点的位数
decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
decimal会精确地存储和计算数值，不进行四舍五入，但也可能出现精度问题
decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10

三、字符串类型

3.1 char类型

用法：

列名 char(L)

说明：

char类型又叫固定长度字符串或定长字符串
L 是可以存储的长度，单位为字符，最大长度值可以为 255
这里的字符与C/C++语言中的字符不同，这里的字符表示是真的一个符号，例如一个汉字也算一个符号，而C/C++中的字符是一个字节。
一个字符在utf-8中占用了3字节；在GBK中占用了2字节。

示例：

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.03 sec)mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.01 sec)mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.01 sec)mysql> insert into tt9 values(101, '中国人');
ERROR 1406 (22001): Data too long for column 'name' at row 1mysql>  select * from tt9;
+------+--------+
| id   | name   |
+------+--------+
|  100 | ab     |
|  101 | 中国   |
+------+--------+
2 rows in set (0.00 sec)

3.2 varchar类型

用法：

列名 varchar(L)

说明：

carchar也叫可变长度字符串或变长字符串。
L表示字符长度，最大长度65535个字节。
我的表默认编码是utf8，一个字符占3字节，那么varchar的参数L理论上的最大值是65535/3=21845，但是类似于字符串，varchar存储结构中有1-3字节用来记录数据大小，所以有效字节数是65532，那么L的最大值就是21844了。（GBK编码的计算方法相同，在此不做计算）

示例：

mysql> create table tt10(id int ,name varchar(6)); --表示这里可以存放6个字符mysql> insert into tt10 values(100, 'hello');mysql> insert into tt10 values(100, '我爱你，中国');mysql> select * from tt10;
+------+--------------------+
| id   |         name       |
+------+--------------------+
| 100  |         hello      |
| 100  |     我爱你，中国     |
+------+--------------------+

3.3 char和varchar比较

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

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

四、时间和日期类型

DATE / date：

格式：YYYY-MM-DD
占用字节：3字节
表示范围：从’1000-01-01’到’9999-12-31’
用途：用于存储日期，不包含时间部分。

DATETIME / datetime：

格式：YYYY-MM-DD HH:MM:SS
占用字节：8字节
表示范围：从’1000-01-01 00:00:00’到’9999-12-31 23:59:59’
用途：用于存储日期和时间，包含时、分、秒。

TIMESTAMP / timestamp：

格式：YYYY-MM-DD HH:MM:SS
占用字节：4字节
表示范围：从’1970-01-01 00:00:01’ UTC到’2038-01-19 03:14:07’ UTC
用途：用于存储时间戳，表示从1970年1月1日（UTC）开始经过的秒数。

mysql> create table birthday ( t1 date, t2 datetime, t3 timestamp default current_timestamp on update current_timestamp);
Query OK, 0 rows affected (0.03 sec)mysql> insert into birthday(t1,t2) values('2000-1-1','2000-1-1 12:12:12');
Query OK, 1 row affected (0.00 sec)mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 2000-01-01 12:12:12 | 2024-08-18 16:55:29 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)mysql>  update birthday set t1='2000-1-2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-02 | 2000-01-01 12:12:12 | 2024-08-18 16:56:03 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

五、枚举和集合类型

5.1 enum类型

用法：

列名 enum('选项1','选项2','选项3',...)

说明：

enum表示枚举、“单选”类型，使用时多选一
该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；
当我们添加枚举值时，也可以添加对应的数字编号。（从1开始）
当插入或更新包含enum类型字段的行时，MySQL会自动检查值是否在允许的枚举值列表中。如果不是，插入或更新操作会失败。
enum类型是大小写敏感的，这意味着’value1’和’Value1’被视为两个不同的值。
enum类型可以有一个默认值，如果没有指定值，MySQL会自动选择第一个枚举值作为默认值
enum类型适用于那些具有有限固定选项的字段，例如性别（男、女）、状态（启用、禁用）等。

5.2 set类型

用法：

列名 set('选项1','选项2','选项3',...)

说明：

set表示集合、“多选”类型
该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，....最多64个
不建议在添加枚举值、集合值的时候采用数字的方式，因为不利于阅读
set类型是大小写敏感的
set类型可以有一个默认值，如果没有指定值，MySQL会自动选择第一个枚举值作为默认值

注意：

SET类型的值必须是唯一的组合。
一旦创建了SET类型，就不能更改它。
如果需要修改SET类型的值，就要删除整个列，然后重新创建它。
如果在添加数据时使用数字标识选项，那这个数字表示的是位图。例如数字7就是0111，代表前三个选项。

集合查询使用find_in_set函数

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

str_list 是用逗号分隔的字符串。

注意：

find_in_set只能查询set的一个选项，如果要查询多个选项，可以用and拼接。

mysql> create table votes(-> username varchar(30),->  hobby set('登山','游泳','篮球','武术'),-> gender enum('男','女'));
Query OK, 0 rows affected (0.03 sec)mysql> insert into votes values('雷锋', '登山,武术', '男');
Query OK, 1 row affected (0.00 sec)mysql> insert into votes values('Juse','登山,武术',2);
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values('张三', '篮球,武术,登山', '男');
Query OK, 1 row affected (0.00 sec)mysql> select * from votes;
+----------+----------------------+--------+
| username | hobby                | gender |
+----------+----------------------+--------+
| 雷锋     | 登山,武术            | 男     |
| Juse     | 登山,武术            | 女     |
| 张三     | 登山,篮球,武术       | 男     |
+----------+----------------------+--------+
3 rows in set (0.00 sec)mysql> select * from votes where find_in_set('登山', hobby) and find_in_set('篮球', hobby);
+----------+----------------------+--------+
| username | hobby                | gender |
+----------+----------------------+--------+
| 张三     | 登山,篮球,武术       | 男     |
+----------+----------------------+--------+
1 row in set (0.00 sec)