目录
一、数据类型分类
二、数值类型
注意:
- 在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 1
mysql> 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: 0
mysql> 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)