【MySQL】常见数据类型
目录
1. 数值类型
默认使用数值类型来定义一列时，是表示为带符号整数，如果以无符号数值来表示的话需要带上unsigned
定义表的列属性时的写法顺序跟语言中变量的顺序是完全反着来的
定义属性列：列名 类型 有无符号
定义变量：有无符号 类型 变量名
1.1 整形类型
1.1.1 bit
位z字段类型，支持定义时后面带一个小括号，里面带上所需要的位数：bit(n)，不带默认n为1，范围在1~64
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| num | int | YES | | NULL | |
| flag | bit(10) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(1, 1), (2, 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.01 sec)
mysql> select * from t1;
+------+------------+
| num | flag |
+------+------------+
| 1 | 0x0001 |
| 2 | 0x0002 |
+------+------------+
2 rows in set (0.00 sec)
mysql> select num, hex(flag) from t1;
+------+-----------+
| num | hex(flag) |
+------+-----------+
| 1 | 1 |
| 2 | 2 |
+------+-----------+
2 rows in set (0.00 sec)
定义为bit类型的列在输出时是按照16进制显示的，可以通过hex函数转成10进制显示
需要注意的是，如果插入的数值不在当前bit位所能表示的范围内时，插入会直接报错，假设当前设置bit类型位数为1，意味着只能表示数字0和1，因为这两个数字只需要一个bie位表示，而其它任何数字的表示所需要的bit位位数都是大于1个的
1.1.2 tinyint、smallint等
tinyint该类型占一个字节，所以它的取值范围为:
有符号范围：-128 到 127
无符号范围: 0 到 255
smallint该类型占两个字节，所以它的取值范围为:
有符号范围: -32,768 到 32,767
无符号范围: 0 到 65,535
mediumint该类型占三个字节，所以它的取值范围为:
有符号范围: -8,388,608 到 8,388,607
无符号范围: 0 到 16,777,215
int 或 integer该类型占四个字节，所以它的取值范围为:
有符号范围: -2,147,483,648 到 2,147,483,647
无符号范围: 0 到 4,294,967,295
bigint该类型占八个字节，所以它的取值范围为:
有符号范围: -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807
无符号范围: 0 到 18,446,744,073,709,551,615
1.2 小数类型
1.2.1 float、double
该类型占四个字节，语法如下：
float[(m, n)] [unsigned] : m指定显示长度，n指定小数位数
也可以不带括号里的，那就使用默认的
指定长度包含了小数位数，所以整数部分长度x实际=m-n，因此当插入的整数位长度超过了x时会报错，此外，如果整数位合法（位数<=x），当小数位不足n位时会自动用0补齐，当小数位大于n位时会进行截断，对第n位进行四舍五入进位，如果进位使得整数部分变长超过了规定的整数位长度时也会报错，所以进位也是在满足条件的情况下才能进位
关于有/无符号表示：
假设定义类型位有符号float(4, 2)，那么该类型的取值范围在-99.99~99.99
如果定义为float(4, 2) unsigned，那么该类型的取值范围在0~99.99，相当于直接砍掉下届也就是负数部分，而上届保持不变
double占用八个字节，总体使用方式和float类似，只是能表示的数值范围更大
1.2.2 decimal
该类型也是浮点数类型，使用方式和语法与float一致，但相较于前者，它的存储精度会更高，当精度过大时，float类型会对其进行四舍五入调整，而decimal不会，存进去什么值取出来也是一样的，因此在处理需要极高精度的计算时，应该考虑使用 decimal或 numeric 类型
float表示的精度n大概是7位，而decimal则最大n是30位，整体表示数位m最大为65，如果n被省略，默认为0，如果m省略则默认是10
decimal(10,0)
2. 字符串类型
2.1 char
该类型为固定长度字符串，语法为：char(len)，其中len表示可以存储的字符串长度，单位为字符，最大值为255
需要与语言中的字符进行区别，由于字符集或编码格式的不同，数据库中的字符可以是一个也可以是多个字节进行表示，而语言中一个字符用一个字节来表示
比如utf-8编码中，ASCII字符占一个字节，而中文占3个字节，但在使用了该字符集的数据库看来，都是一个字符，为了能保证能够存下不同类型的字符，在开辟空间是是按照最大字符所占空间的字节数来开辟的，utf-8是三个字节，即使存储时全是一个字节的ASCII字符
2.2 varchar
该类型表示变成字符串类型，语法与char一致：varchar(len)，len表示字符串长度，最大长度为65535个字节
2.2.1 长度说明
注意这里是字节而不是字符，需要除以当前字符集所能表示字符的最大存储空间所占的字节数才是真正可以保存字符的个数
这里的len有多大，完全取决于数据库所使用的字符集，当前版本使用的字符集是utf8mb4，字符最大存储空间所占的字节数为4，所以65535 / 4 ≈ 16383个字符
mysql> create table t5 (name varchar(1111111));
ERROR 1074 (42000): Column length too big for column 'name' (max = 16383);
对于需要存储大量文本数据的列，应该使用 TEXT 类型而不是 VARCHAR
use BLOB or TEXT instead
上述除法的结果不是整除，实际上还余三个字节，这三个字节是用来记录存储的字符串长度的，所以实际用于存储字符串的字节数为65532，要用这个值去除得到的才是真正的可以保存的最长长度
除此之外，最长长度还会受到当前行的其他属性类型所占的字节数影响，如果一行不只只有这一个varchar属性列，还包含其它的属性，那么最大值会变得更小，总字节数减去当前其它属性类型所占的字节数再除，所以字符数会更少
2.3 二者区别
在使用上没什么区别，区别在于存储层面上，使用char所指定的那个数字n，就有点像定义了一个长度为n的定长数组，只要不越界插入，这块空间随便怎么用，越界就报错，而varchar所指定的n，就相当于规定了一块空间的上限，后续使用多少空间就开辟多少空间，实际开辟空间的长度不能超过n，越界了也同样报错
换句话说就是，char是直接开辟一块长度为n的定长空间，而varchar需要根据实际要存储的字符串长度进行动态开辟空间，长度不能超过n，这是二者之间主要区别
除此之外，char在开辟空间时要以当前字符集的所能表示的最大字节数为基准值开辟，因为要有能力保存所有字符，而varchar则是根据字符串内容动态决定
如何选择哪一种呢？
- 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
- 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去
- 定长的磁盘空间比较浪费，但是效率高，不需要动态维护字符串的长度
- 变长的磁盘空间比较节省，但是效率低，因为要动态维护最大长度和存储字符串的长度
- 定长的意义是，直接开辟好对应的空间
- 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少
3. 日期和时间类型
常用的日期有如下三个：
- date :日期 ‘yyyy-mm-dd’ ，占用三字节
- datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节
- timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节
其中前两个是用来记录固定日期或者日期+时间，需要用户手动插入，而最后一个则是在插入或者更新当前条目时由系统自动设置的，无需用户手动插入
选择建议：
- 如果只需要记录日期，不需要时间信息，则使用date类型
- 如果需要同时记录日期和时间，且对时间范围没有特别限制，则使用datetime类型
- 如果需要自动记录数据行的创建或最后修改时间，且时间范围在1970年至今内，则使用timestamp类型
4. enum和set
4.1 enum
语法如下：
enum：枚举，“单选”类型；
enum(‘选项1’,‘选项2’,‘选项3’,…);
该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当添加枚举值时，也可以添加对应的数字编号，其实就是数组下标，只不过从1开始
比如问卷调查一些条目给你几个选项让你选，最终只能选一个
如果插入的数据或者数字在不在枚举范围内会报错
4.2 set
语法如下：
set：集合，“多选”类型；
set(‘选项值1’,‘选项值2’,‘选项值3’, …);
该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，每个选项值依次对应如下数字：1,2,4,8,16,32，…最多64个
这些数字实际上是一个个比特位，也就是2的幂，哪几个比特位为1就意味着选择哪几个值，类似位图结构
其实就是把单选变成了多选而已，若多选中间使用逗号隔开
不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。
4.3 使用实例
创建表：
mysql> create table t5 (
-> sex enum('男','女'),
-> hobby set('代码', '游戏', '足球', '篮球')
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> desc t5;
+-------+------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------------+------+-----+---------+-------+
| sex | enum('男','女') | YES | | NULL | |
| hobby | set('代码','游戏','足球','篮球') | YES | | NULL | |
+-------+------------------------------------------+------+-----+---------+-------+
插入数据：
mysql> insert into t5 values ('男', '代码,游戏');
Query OK, 1 row affected (0.01 sec)
# 四个选项有四个比特位，全1表示全选，转成十进制就是15
# 注意这里的比特位顺序是从左向右表示，也就是左边是最低比特位
# 依次匹配：代码,游戏,足球,篮球
mysql> insert into t5 values (2, 15);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+------+-----------------------------+
| sex | hobby |
+------+-----------------------------+
| 男 | 代码,游戏 |
| 女 | 代码,游戏,足球,篮球 |
+------+-----------------------------+
2 rows in set (0.00 sec)
4.4 enum查找
把所有的性别为男的查找出来：
mysql> select * from t5 where sex='男';
+------+---------------+
| sex | hobby |
+------+---------------+
| 男 | 代码,游戏 |
| 男 | 代码,游戏 |
| 男 | 足球,篮球 |
| 男 | 代码 |
+------+---------------+
4 rows in set (0.00 sec)
也可以使用数字的方式，也就是where sex=1;，这里的where子句相当于一个判断条件，把满足后面表达式的记录筛选出来
4.5 set查找
假设当前表中的记录为：
mysql> select * from t5;
+------+-----------------------------+
| sex | hobby |
+------+-----------------------------+
| 男 | 代码,游戏 |
| 女 | 代码,游戏,足球,篮球 |
| 女 | 代码,游戏,足球 |
| 女 | 代码,游戏 |
| 男 | 代码,游戏 |
| 男 | 足球,篮球 |
| 男 | 代码 |
+------+-----------------------------+
把hobby有代码的人筛选出来：
mysql> select * from t5 where hobby='代码';
+------+--------+
| sex | hobby |
+------+--------+
| 男 | 代码 |
+------+--------+
1 row in set (0.00 sec)
# 如果想匹配多个，中间使用逗号隔开，如：hobby='代码,游戏'
也可以按照数字的方式进行筛选
这里输出的结果貌似与预期不符，因为期望的是hobby包含代码的，而不是hobby仅仅只有代码的，原因在于这种查询方式是严格/绝对匹配，也就是只匹配用户所指定的hobby，实际的记录中的hobby不管是匹配多了还是少了都会匹配失败，如果要查询是否包含了某个hobby，也就是查询在集合中是否包含某个数据时需要用到一个筛选函数：find_in_set
4.5.1 find_in_set
该函数的作用是判断某数据是否被包含在某个集合中，如果被包含则返回所处的下标，否则返回0，语法如下：
find_in_set(element, ele_list);
所以下标是从1开始而不是0，为了区分返回结果
ele_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)
这里是查看字符a是否在集合abc中，结果显示1表示在，且下标为1，不在会返回0：
mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
只能查一个元素是否在集合中，无法同时查看多个
注意：select后面可以执行函数或者表达式，执行表达式：
mysql> select 1+1;
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
了解了这个函数之后就可以查询包含了对应hobby的记录了：
mysql> select * from t5 where find_in_set('代码', hobby);
+------+-----------------------------+
| sex | hobby |
+------+-----------------------------+
| 男 | 代码,游戏 |
| 女 | 代码,游戏,足球,篮球 |
| 女 | 代码,游戏,足球 |
| 女 | 代码,游戏 |
| 男 | 代码,游戏 |
| 男 | 代码 |
+------+-----------------------------+
6 rows in set (0.00 sec)
这样就可以找出所有hobby包含了代码的记录了，但如果想查询hobby里同时包含代码和游戏的记录可以使用下述方式：
mysql> select * from t5 where find_in_set('代码', hobby) and find_in_set('游戏', hobby);
+------+-----------------------------+
| sex | hobby |
+------+-----------------------------+
| 男 | 代码,游戏 |
| 女 | 代码,游戏,足球,篮球 |
| 女 | 代码,游戏,足球 |
| 女 | 代码,游戏 |
| 男 | 代码,游戏 |
+------+-----------------------------+
5 rows in set (0.00 sec)
类似语言那样，使用and符号，也就是逻辑与，只有两边同时满足才筛选，这样就可以选出符合条件的记录了
5. 数据类型的约束
当使用了某种数值类型作为列属性时，如果往表中插入的数值越界了或者说超出了当前类型所能表示的最大数值范围等等类似的操作的时候，MySQL会直接报错，不让这么操作，而不是像语言那样数值越界会发生截断或者隐式类型转换，对于其它类型如果插入的数据不合法也是一样的道理
原因在于这种做法能够有效保证插入到数据库的中数据一定是合法或者说有效的，因此在MySQL中，数据类型隐式地提供一种约束的能力，约束用户必须正确地插入数据，即使用户并没有很规范地进行插入，它也能通过报错手段保证插入的合法性
当给一个数据库设置足够多的约束，就能保证数据库中的数据是可预期且完整的，比如说当前列的数据类型为无符号tinyint，那其中保存的数值范围一定是在0~255之间的，不可能存在范围之外的数值，这是可预期的表现，其次对于不合法的插入会直接报错，不让用户插入非法数据，这说明插入进去的数据一定都是有效完整的，这是完整性的表现
原文地址：https://blog.csdn.net/weixin_66672501/article/details/142370806
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！