技术干货-MYSQL数据类型详解

mysql> create table tt1;
ERROR 4028 (HY000): A table must have at least one visible column.
mysql> create table tt1(num tinyint);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into ttl values(1);
ERROR 1146 (42S02): Table 'test1.ttl' doesn't exist
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> insert into tt1 values(-128);
Query OK, 1 row affected (0.01 sec)

mysql> insert into tt1 values(-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into tt1 values(127);
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt1;
+------+
| num  |
+------+
|    1 |
| -128 |
|  127 |
+------+
3 rows in set (0.00 sec)

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)

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

mysql> insert into ttl value(1,1);
ERROR 1146 (42S02): Table 'test.ttl' doesn't exist
mysql> insert into tt1 value(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt1;
+------+------------+
| id   | a          |
+------+------------+
|    1 | 0x01       |
+------+------------+
1 row in set (0.00 sec)

mysql> insert into tt1 value(10,10);
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt1;
+------+------------+
| id   | a          |
+------+------------+
|    1 | 0x01       |
|   10 | 0x0A       |
+------+------------+
2 rows in set (0.00 sec)

mysql> insert into tt1 value(65,65);
Query OK, 1 row affected (0.00 sec)

mysql> select *from tt1;
+------+------------+
| id   | a          |
+------+------------+
|    1 | 0x01       |
|   10 | 0x0A       |
|   65 | 0x41       |
+------+------------+
3 rows in set (0.00 sec)

mysql> SELECT id, a, ASCII(a) AS ascii_code FROM tt1;
+------+------------+------------+
| id   | a          | ascii_code |
+------+------------+------------+
|    1 | 0x01       |          1 |
|   10 | 0x0A       |         10 |
|   65 | 0x41       |         65 |
+------+------------+------------+
3 rows in set (0.00 sec)

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

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

mysql> insert into tt7 values(100, -0.1);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Error | 1264 | Out of range value for column 'salary' at row 1 |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> create table tt8 ( id int, salary float(10,8), salary2
decimal(10,8));
mysql> insert into tt8 values(100,23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt8;
+------+-------------+-------------+
| id | salary | salary2 |
+------+-------------+-------------+
| 100 | 23.12345695 | 23.12345612 | # 发现decimal的精度更准确，因此如果我们希望某
个数据表示高精度，选择decimal
+------+-------------+-------------+

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

mysql> create table tt9(id int, name char(2));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tt9 values(100, 'ab');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt9 values(101, '中国');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt9;
+------+--------+
| id | name |
+------+--------+
| 100 | ab |
| 101 | 中国 |
+------+--------+

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

mysql> create table tt11(name varchar(21845))charset=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql>

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

mysql> insert into bir (t1,t2)values('2025-10-27','2025-10-27 12:1:1');
Query OK, 1 row affected (0.01 sec)

mysql> select *from bir;
+------------+---------------------+------+
| t1         | t2                  | t3   |
+------------+---------------------+------+
| 2025-10-27 | 2025-10-27 12:01:01 | NULL |
+------------+---------------------+------+
1 row in set (0.00 sec)

mysql> -- 修改表结构，重新定义t3
mysql> ALTER TABLE bir
    -> MODIFY COLUMN t3 timestamp
    -> DEFAULT CURRENT_TIMESTAMP  -- 插入时自动填当前时间
    -> ON UPDATE CURRENT_TIMESTAMP;  -- 更新时自动更新为当前时间
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from bir;
+------------+---------------------+------+
| t1         | t2                  | t3   |
+------------+---------------------+------+
| 2025-10-27 | 2025-10-27 12:01:01 | NULL |
+------------+---------------------+------+
1 row in set (0.00 sec)

mysql> -- 插入新记录（不指定t3）
mysql> INSERT INTO bir (t1, t2) VALUES ('2025-10-28', '2025-10-28 13:00:00');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> -- 更新已有记录
mysql> UPDATE bir SET t1 = '2025-10-29' WHERE t1 = '2025-10-27';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from bir;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2025-10-29 | 2025-10-27 12:01:01 | 2025-10-27 23:27:41 |
| 2025-10-28 | 2025-10-28 13:00:00 | 2025-10-27 23:27:40 |
+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

CREATE TABLE 表名 (
  字段名 ENUM('选项1', '选项2', '选项3', ...) [约束]
);

-- 创建包含ENUM字段的表（性别：男/女/保密）
CREATE TABLE user (
  id INT PRIMARY KEY,
  gender ENUM('男', '女', '保密') NOT NULL DEFAULT '保密'
);

-- 插入数据（只能选一个选项）
INSERT INTO user (id, gender) VALUES
(1, '男'),    -- 合法
(2, '女'),    -- 合法
(3, '保密');  -- 合法（使用默认值也可）

-- 错误：插入未定义的选项
INSERT INTO user (id, gender) VALUES (4, '其他');  -- 报错（值不在ENUM列表中）

CREATE TABLE 表名 (
  字段名 SET('选项1', '选项2', '选项3', ...) [约束]
);

mysql> create table use_hbby(
    -> name varchar(20),
    -> hobby set('登山','游泳','篮球','武术'),
    -> gender enum('男','女') not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into use_hbby values('雷锋', '登山,武术', '男');

mysql> insert into use_hbby values('Juse','登山,武术',2);

mysql> insert into use_hbby values('胡志', '武术', '男');

mysql> insert into use_hbby values('程坤', '游泳', '男');

mysql> insert into use_hbby values('小红','登山','女');

mysql> select * from use_hbby where gender=2;
+------+-----------+--------+
| name | hobby     | gender |
+------+-----------+--------+
| Juse | 登山,武术 | 女     |
| 小红 | 登山      | 女     |
+------+-----------+--------+