【MySQL】数据类型

1.整数类型（tinyint为例）

在MySQL中，整数可以指定有符号/无符号，默认是有符号的；可以通过unsigned来说明某个字段无符号

1.1 有符号

• 范围：-128~127

mysql> create table t1(num tinyint);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 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)

1.2 无符号

• 范围：0~255

mysql> create table d1(num tinyint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into d1 values(255);
Query OK, 1 row affected (0.00 sec)

mysql> insert into d1 values(256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from d1;
+------+
| num  |
+------+
|  255 |
+------+
1 row in set (0.00 sec)

2.bit 类型

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

mysql> create table t4 ( id int, a bit(8));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t4 values(10, 10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4; //发现很怪异的现象，a的数据10没有出现
+------+------+
| id | a 	  |
+------+------+
| 10 | 		  |
+------+------+
1 row in set (0.00 sec)

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

mysql> insert into t4 values(65, 65);
mysql> select * from t4;
+------+------+
| id | a      |
+------+------+
| 10 | 		  |
| 65 | A 	  |
+------+------+

• 如果我们只需要存0/1，就可以适用bit(1).这样节省空间

mysql> create table t5(gender bit(1));

mysql> insert into t5 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(2); // 当插入2时，已经越界了
ERROR 1406 (22001): Data too long for column 'gender' at row 1

3. 浮点数类型

3.1 float

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

3.1.1有符号

• float(4,2)的范围是 -99.99~99.99，MySQL在保存值时会自动进行四舍五入

mysql> create table t6(id int, salary float(4,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t6 values(100, -99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values(101, -99.991); //多的这一点被拿掉了
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+--------+
| id | salary 	|
+------+--------+
| 100 | -99.99 	|
| 101 | -99.99 	|
+------+--------+
2 rows in set (0.00 sec)

3.1.2 无符号

• float(4,2)的范围是0~99.99，MySQL在保存值时会自动进行四舍五入

mysql> create table d1(id int, salary float(4,2) unsigned);
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> insert into d1 values (1,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into d1 values (1,10.0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into d1 values (1,-1);//超过范围
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into d1 values (1,99.995);//四舍五入后超过范围
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select *from d1;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    1 |  10.00 |
+------+--------+
2 rows in set (0.00 sec)

3.2 decimal

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

decimal和float基本一样，唯一的不同是：decimal的精度比float高

mysql> create table t8 ( id int, salary float(10,8), salary2 decimal(10,8));

mysql> insert into t8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
| 100  | 23.12345695 | 23.12345612 | //发现decimal的精度更准确，因此如果我们希望某个数据表示高精度，选择decimal
+------+-------------+-------------+

4.字符串类型

4.1 char

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

mysql> create table t9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9;
+------+--------+
| id   | name   |
+------+--------+
| 100  | ab     |
| 101  | 中国   |
+------+--------+

//char(2):表示最多可以存2个字符，可以是字母/汉字，最多只能255
mysql> create table tt10(id int ,name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

4.2 varchar

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

mysql> create table t10(id int ,name varchar(6)); //表示这里可以存放6个字符

mysql> insert into t10 values(100, 'hello');

mysql> insert into t10 values(100, '我爱你，中国');

mysql> select * from t10;
+------+--------------------+
| id   | name               |
+------+--------------------+
| 100 | hello               |
| 100 | 我爱你，中国           |
+------+--------------------+

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字
  节数是65532。
• 当表的编码是utf8时，varchar(n)参数的n最大是65532/3=21844；当表的编码是gbk时，varchar(n)参数的n最大是65532/2=32766.

mysql> create table t11(name varchar(21845))charset=utf8; //验证了utf8确实是不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the usedtable type, not counting BLOBs, is 65535. You have to change some columns toTEXT or BLOBs

mysql> create table t11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

4.3 char和varchar的比较

• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。

5.日期类型

常用的日期：

date：日期’yyyy-mm-dd’，占用三字节
datetime 时间日期格式’yyyy-mm-dd HH:ii:ss’，占用八字节
timestamp：时间戳，从1970年开始的‘yyyy-mm-dd HH:ii:ss’，占用四字节

mysql> create table birthday (t1 date, t2 datetime, t3 timestamp);
Query OK, 0 rows affected (0.01 sec)

//插入数据：
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); //插入两种时间
Query OK, 1 row affected (0.00 sec)

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 1997-07-01 | 2008-08-08 12:01:01 | 2025-05-16 18:28:55 | //添加数据时，时间戳自动补上当前时间
+------------+---------------------+---------------------+

//更新数据：
mysql> update birthday set t1='2000-1-1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1 | t2 | t3 |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2025-05-16 18:32:09 | //更新数据，时间戳会更新成当前时间
+------------+---------------------+---------------------+

6.enum 和 set 类型

enum 枚举 “单选”类型
set 位图 “多选”类型

//创建表
mysql> create table votes(
-> username varchar(30),
-> hobby set('登山','游泳','篮球','武术'), //注意：使用数字标识每个爱好的时候，想想Linux权限，采用比特位位置来个set中的爱好对应起来
-> gender enum('男','女')); //注意：使用数字标识的时候，就是正常的数组下标
Query OK, 0 rows affected (0.02 sec)

//插入数据
insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);
insert into votes values('LiLei','登山',1);
insert into votes values('LiLei','篮球','男');
insert into votes values('HanMeiMei','游泳','女');
+-----------+---------------+--------+
| username | hobby | gender |
+-----------+---------------+--------+
| 雷锋       | 登山,武术      | 男      |
| Juse      | 登山,武术       | 女      |
| LiLei     | 登山           | 男 	|
| LiLei 	| 篮球 			| 男	 |
| HanMeiMei | 游泳 			| 女 	|
+-----------+---------------+--------+

//查找所有登山的人
mysql> select * from votes where hobby='登山';
+----------+--------+--------+
| username | hobby | gender |
+----------+--------+--------+
| LiLei 	| 登山 	| 男 	| //不能查询出所有登山的人
+----------+--------+--------+

• 集合查询使用find_ in_ set函数
• find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；

mysql> select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby 			| gender |
+----------+---------------+--------+
| 雷锋 		| 登山,武术		 | 男	 |
| Juse 		| 登山,武术 		| 女 	|
| LiLei 	| 登山 			| 男 	|
+----------+---------------+--------+