MySQL之数据类型

目录
一：数据类型分类 
二:数值类型 
1. tinyint类型 
2.bit类型
3.小数类型 
 1. float
2.decimal 
三:字符串类型 
1.char
2.varchar 
3.char和varchar比力 
4.日期和时间类型 
5.enum和set

接下来的日子会顺顺遂利，万事胜意，生存清朗-----------林辞忧
一：数据类型分类 

二:数值类型 

1. tinyint类型 

这里int相关类型以tinyint为例，其余皆可同理
1.在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
2.可以通过unsigned来阐明某个字段是无符号的
3.越界插入数据测试

1. mysql> create database d1;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> use d1;
5. Database changed
6. mysql> show tables;
7. Empty set (0.00 sec)
9. mysql> create table if not exists t1(
10.     -> id tinyint
11.     -> );
12. Query OK, 0 rows affected (0.02 sec)
14. mysql> insert into t1 values (-128);
15. Query OK, 1 row affected (0.00 sec)
17. mysql> insert into t1 values (127);
18. Query OK, 1 row affected (0.00 sec)
20. mysql> insert into t1 values (128);
21. ERROR 1264 (22003): Out of range value for column 'id' at row 1
22. mysql> insert into t1 values (-129);
23. ERROR 1264 (22003): Out of range value for column 'id' at row 1
24. mysql> select * from t1;
25. +------+
26. | id   |
27. +------+
28. | -128 |
29. |  127 |
30. +------+
32. mysql> create table if not exists t2(
33.     -> id tinyint unsigned
34.     -> );
35. Query OK, 0 rows affected (0.03 sec)
37. mysql> insert into t2 values (0);
38. Query OK, 1 row affected (0.01 sec)
40. mysql> insert into t2 values (-1);
41. ERROR 1264 (22003): Out of range value for column 'id' at row 1
42. mysql> insert into t2 values (255);
43. Query OK, 1 row affected (0.00 sec)
45. mysql> insert into t2 values (256);
46. ERROR 1264 (22003): Out of range value for column 'id' at row 1
47. mysql> select* from t2;
48. +------+
49. | id   |
50. +------+
51. |    0 |
52. |  255 |
53. +------+

复制代码

会发现如果我们向mysql特定的类型中插入不合法的数据，MySQL一般都是直接拦截，不让进行对应的操作;相反，如果我们已经有数据被成功插入到mysql中，插入的数据一定是合法的.所以，在mysql中，一般而言，数据类型自己也是一种约束 
2.bit类型

1.基本语法:bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1
2.实例：

1. mysql> create table if not exists t3(
2.     -> id int,
3.     -> online bit(1)
4.     -> );
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> insert into t3 (id,online) values (1,0);
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t3 (id,online) values (2,1);
11. Query OK, 1 row affected (0.01 sec)
13. mysql> insert into t3 (id,online) values (3,2);
14. ERROR 1406 (22001): Data too long for column 'online' at row 1
15. mysql> select* from t3;
16. +------+--------+
17. | id   | online |
18. +------+--------+
19. |    1 |        |
20. |    2 |       |
21. +------+--------+

复制代码

这里对于插入越界的数据仍然是拦截不让插入，另有这里的0/1不显示，这是由于bit字段在显示时，是按照ASCII码对应的值显示，这里的0/1对应的ASCII码是不显示的
如要显示的话，就应该以十进制的方式显示

1. mysql> select id, hex(online) from t3;
2. +------+-------------+
3. | id   | hex(online) |
4. +------+-------------+
5. |    1 | 0           |
6. |    2 | 1           |
7. +------+-------------+

复制代码

1. mysql> alter table t3 modify online bit(64);
2. Query OK, 2 rows affected (0.07 sec)
3. Records: 2  Duplicates: 0  Warnings: 0
5. mysql> insert into t3 (id,online) values (1,A);
6. ERROR 1054 (42S22): Unknown column 'A' in 'field list'
7. mysql> insert into t3 (id,online) values (1,'A');
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t3 (id,online) values (1,98);
11. Query OK, 1 row affected (0.01 sec)
13. mysql> select* from t3;
14. +------+----------+
15. | id   | online   |
16. +------+----------+
17. |    1 |          |
18. |    2 |         |
19. |    1 |        A |
20. |    1 |        b |
21. +------+----------+

复制代码

3.小数类型 

 1. float

语法:float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节
实例:小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在生存值时会进行四舍五入

1. mysql> create table if not exists t4(
2.     -> id int,
3.     -> salary float(4,2)
4.     -> );
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> insert into t4 (id,salary) values (1,-99.99);
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t4 (id,salary) values (2,99.99);
11. Query OK, 1 row affected (0.01 sec)
13. mysql> insert into t4 (id,salary) values (2,23.145);
14. Query OK, 1 row affected (0.00 sec)
16. mysql> insert into t4 (id,salary) values (2,-99.994);
17. Query OK, 1 row affected (0.01 sec)
19. mysql> insert into t4 (id,salary) values (2,-99.995);
20. ERROR 1264 (22003): Out of range value for column 'salary' at row 1
21. mysql> insert into t4 (id,salary) values (2,99.994);
22. Query OK, 1 row affected (0.00 sec)
24. mysql> insert into t4 (id,salary) values (2,99.995);
25. ERROR 1264 (22003): Out of range value for column 'salary' at row 1
26. mysql> select* from t4;
27. +------+--------+
28. | id   | salary |
29. +------+--------+
30. |    1 | -99.99 |
31. |    2 |  99.99 |
32. |    2 |  23.14 |
33. |    2 | -99.99 |
34. |    2 |  99.99 |
35. +------+--------+

复制代码

会发现对于凌驾指定位数的，会四舍五入进行生存，生存后的数据只要在范围内就可以正常插入，不在范围的就会直接拦截，何况float对于数据偶尔是不能正确生存的 
如果界说的是float(4,2) unsigned 这时，由于把它指定为无符号的数，范围是 0 ~ 99.99 
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表示的精度不一样

1. mysql> create table if not exists t5(
2.     -> id int,
3.     -> s1 float(10,8),
4.     -> s2 decimal(10,8)
5.     -> );
6. Query OK, 0 rows affected (0.02 sec)
8. mysql> insert into t5 (id,s1,s2) values (1,23.12345612,23.12345612);
9. Query OK, 1 row affected (0.00 sec)
11. mysql> select* from t5;
12. +------+-------------+-------------+
13. | id   | s1          | s2          |
14. +------+-------------+-------------+
15. |    1 | 23.12345695 | 23.12345612 |
16. +------+-------------+-------------+

复制代码

float表示的精度约莫是7位。
decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。
发起：如果希望小数的精度高，推荐使用decimal
三:字符串类型 

1.char

语法:char(L): 固定长度字符串，L是可以存储的长度，单元为字符，最大长度值可以为255
在utf8编码当中，一个汉字为一个字符，一个字符为三字节
实例:

1. mysql> create table t6(
2.     -> id int,
3.     -> name char(3)
4.     -> );
5. Query OK, 0 rows affected (0.02 sec)
7. mysql> insert into t6 (id,name) values (1,'中');
8. Query OK, 1 row affected (0.00 sec)
10. mysql> insert into t6 (id,name) values (1,'中国');
11. Query OK, 1 row affected (0.00 sec)
13. mysql> insert into t6 (id,name) values (1,'中国人');
14. Query OK, 1 row affected (0.00 sec)
16. mysql> insert into t6 (id,name) values (1,'中国人民');
17. ERROR 1406 (22001): Data too long for column 'name' at row 1

复制代码

2.varchar 

1.语法:varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节
2.关于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字节）

1. mysql> create table tt11(name varchar(21845))charset=utf8; --验证了utf8确实是不能超过21844
2. ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
3. not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
5. mysql> create table tt11(name varchar(21844)) charset=utf8;
6. Query OK, 0 rows affected (0.01 sec)

复制代码

3.char和varchar比力 

4.日期和时间类型 

1.常用的日期有如下三个：
date :日期 'yyyy-mm-dd' ，占用三字节
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节
timestamp:时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节
时间戳不需要自己维护，会主动更新的
 2.实例:

1. mysql> create table t7(
2.     -> t1 date,
3.     -> t2 datetime,
4.     -> t3 timestamp
5.     -> );
6. Query OK, 0 rows affected (0.03 sec)
8. mysql> insert into t7 (t1,t2) values ('2003-08-15','2008-08-10 12:01:01');
9. Query OK, 1 row affected (0.01 sec)
11. mysql> select* from t7;
12. +------------+---------------------+---------------------+
13. | t1         | t2                  | t3                  |
14. +------------+---------------------+---------------------+
15. | 2003-08-15 | 2008-08-10 12:01:01 | 2024-09-13 09:33:34 |
16. +------------+---------------------+---------------------+
17. 1 row in set (0.01 sec)
19. mysql> update t7 set t1='1999-12-31'; #更新数据;
20. Query OK, 1 row affected (0.00 sec)
21. Rows matched: 1  Changed: 1  Warnings: 0
23. mysql> select* from t7;
24. +------------+---------------------+---------------------+
25. | t1         | t2                  | t3                  |
26. +------------+---------------------+---------------------+
27. | 1999-12-31 | 2008-08-10 12:01:01 | 2024-09-13 09:34:30 |
28. +------------+---------------------+---------------------+
29. 1 row in set (0.00 sec)
31. mysql> create table if not exists t8(
32.     -> context text,
33.     -> time timestamp
34.     -> );
35. Query OK, 0 rows affected (0.03 sec)
37. mysql> insert into t8 (context) values ('中国人民');
38. Query OK, 1 row affected (0.00 sec)
40. mysql> select* from t8;
41. +--------------+---------------------+
42. | context      | time                |
43. +--------------+---------------------+
44. | 中国人民     | 2024-09-13 09:36:39 |
45. +--------------+---------------------+
46. 1 row in set (0.00 sec)
48. mysql> update t8 set context='中国人民站起来了'; #更新数据
49. Query OK, 1 row affected (0.00 sec)
50. Rows matched: 1  Changed: 1  Warnings: 0
52. mysql> select* from t8;
53. +--------------------------+---------------------+
54. | context                  | time                |
55. +--------------------------+---------------------+
56. | 中国人民站起来了         | 2024-09-13 09:37:27 |
57. +--------------------------+---------------------+
58. 1 row in set (0.00 sec)

复制代码

3.更新操作  update 表名 set 列名=新内容;
5.enum和set

1.语法:enum：枚举，“单选”类型；
enum('选项1','选项2','选项3',...);
该设定只是提供了若干个选项的值，最终一个单元格中，现实只存储了其中一个值；而且出于服从考虑，这些值现实存储的是“数字”，由于这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号
set：集合，“多选”类型；
set('选项值1','选项值2','选项值3', ...);
该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于服从考虑，这些值
现实存储的是“数字”，由于这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，.... 最多64个。
2.实例:

1. mysql> create table if not exists t9(
2.     -> name varchar(20),
3.     -> gender enum('男','女'),
4.     -> hobby set('代码','羽毛球','乒乓球','足球')
5.     -> );
6. Query OK, 0 rows affected (0.03 sec)
8. mysql> insert into t9 (name,gender,hobby) values ('刘备','男','代码');
9. Query OK, 1 row affected (0.00 sec)
11. mysql> insert into t9 (name,gender,hobby) values ('孙尚香','女','代码,羽毛球');
12. Query OK, 1 row affected (0.00 sec)
14. #插入enum和set中不存在的值就会报错
15. mysql> insert into t9 (name,gender,hobby) values ('刘禅','unknown','代码,羽毛球');
16. ERROR 1265 (01000): Data truncated for column 'gender' at row 1
17. mysql> insert into t9 (name,gender,hobby) values ('刘禅','unknown','跑步');
18. ERROR 1265 (01000): Data truncated for column 'gender' at row 1
21. #enum在插入时可以写枚举常量值，也可以写常量对应下标(从1开始)
22. mysql> insert into t9 (name,gender,hobby) values ('曹操',1,'代码');
23. Query OK, 1 row affected (0.01 sec)
25. mysql> insert into t9 (name,gender,hobby) values ('貂蝉',2,'代码');
26. Query OK, 1 row affected (0.00 sec)
28. mysql> select* from t9;
29. +-----------+--------+------------------+
30. | name      | gender | hobby            |
31. +-----------+--------+------------------+
32. | 刘备      | 男     | 代码             |
33. | 孙尚香    | 女     | 代码,羽毛球      |
34. | 曹操      | 男     | 代码             |
35. | 貂蝉      | 女     | 代码             |
36. +-----------+--------+------------------+
39. #set值在用数字时，是按照数字二进制bit位是否为1进行的
40. mysql> insert into t9 (name,gender,hobby) values ('孙权',1,1);
41. Query OK, 1 row affected (0.00 sec)
43. mysql> insert into t9 (name,gender,hobby) values ('周瑜',1,2);
44. Query OK, 1 row affected (0.00 sec)
46. mysql> insert into t9 (name,gender,hobby) values ('赵云',1,3);
47. Query OK, 1 row affected (0.00 sec)
49. mysql> insert into t9 (name,gender,hobby) values ('张飞',1,4);
50. Query OK, 1 row affected (0.01 sec)
52. mysql> insert into t9 (name,gender,hobby) values ('关羽',1,5);
53. Query OK, 1 row affected (0.00 sec)
55. mysql> insert into t9 (name,gender,hobby) values ('黄忠',1,6);
56. Query OK, 1 row affected (0.00 sec)
58. mysql> insert into t9 (name,gender,hobby) values ('马超',1,15);
59. Query OK, 1 row affected (0.01 sec)
61. mysql> select* from t9;
62. +-----------+--------+-----------------------------------+
63. | name      | gender | hobby                             |
64. +-----------+--------+-----------------------------------+
65. | 刘备      | 男     | 代码                              |
66. | 孙尚香    | 女     | 代码,羽毛球                       |
67. | 曹操      | 男     | 代码                              |
68. | 貂蝉      | 女     | 代码                              |
69. | 孙权      | 男     | 代码                              |
70. | 周瑜      | 男     | 羽毛球                            |
71. | 赵云      | 男     | 代码,羽毛球                       |
72. | 张飞      | 男     | 乒乓球                            |
73. | 关羽      | 男     | 代码,乒乓球                       |
74. | 黄忠      | 男     | 羽毛球,乒乓球                     |
75. | 马超      | 男     | 代码,羽毛球,乒乓球,足球           |
76. +-----------+--------+-----------------------------------+

复制代码

3.查找
使用select* from 表名 where 列名=常量/数字如许查的话，是严酷匹配去查找的，有多兴趣的是查不出来的
此时就可以使用find_in_set函数来查询
find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。
如1:查询爱好中包含乒乓球的人

1. ysql> select* from t9 where find_in_set('乒乓球',hobby);
3. +--------+--------+-----------------------------------+
4. | name   | gender | hobby                             |
5. +--------+--------+-----------------------------------+
6. | 张飞   | 男     | 乒乓球                            |
7. | 关羽   | 男     | 代码,乒乓球                       |
8. | 黄忠   | 男     | 羽毛球,乒乓球                     |
9. | 马超   | 男     | 代码,羽毛球,乒乓球,足球           |
10. +--------+--------+-----------------------------------+

复制代码

 如2：查询爱好中有代码和羽毛球的人

1. mysql> select* from t9 where find_in_set('代码',hobby) and find_in_set('羽毛球',hobby);
2. +-----------+--------+-----------------------------------+
3. | name      | gender | hobby                             |
4. +-----------+--------+-----------------------------------+
5. | 孙尚香    | 女     | 代码,羽毛球                       |
6. | 赵云      | 男     | 代码,羽毛球                       |
7. | 马超      | 男     | 代码,羽毛球,乒乓球,足球           |
8. +-----------+--------+-----------------------------------+

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。