杰霸霸博客

主题

MySQL数据类型 ​

1. 整型类型 ​

类型占用空间范围
TINYINT1字节有符号：-128~127
无符号：0~255
SMALLINT2字节有符号：-32768~32767
无符号：0~65535
MEDIUMINT3字节有符号：-8388608~8388607
无符号：0~16777215
INT4字节有符号：-2³¹~2³¹-1
无符号：0~2³²-1
BIGINT8字节有符号：-2⁶³~2⁶³-1
无符号：0~2⁶⁴-1
sql
create table t_test01(
    a int unsigned,
    b int signed
);

1.1 Int属性 ​

  1. unsigned和signed 是否有符号，默认是signed，不推荐使用unsigned
  2. zerofill 自动在前面填充0，只是作为显示，类型不是字符串：
    Alt text
  3. auto_increment 自增属性，每张表只能有一个，必须是索引的一部分(比如主键索引)，建议类型为bigint。MySQL8.0会持久化当前最新的自增值，重启仍然不变。

2. 浮点型 ​

Alt text 其中FLOAT(M,D)/DOUBLE(M,D)/DECIMAL(M,D)表示显示M位整数，其中D位位于小数点后面。推荐直接使用DECIMAL，避免出现Java程序中数据统计不准确问题。

2.1 相关函数 ​

sh
## 取整函数
mysql> select floor(-1.9), round(1,4), round(1.6);
+-------------+------------+------------+
| floor(-1.9) | round(1,4) | round(1.6) |
+-------------+------------+------------+
|          -2 |          1 |          2 |
+-------------+------------+------------+
1 row in set (0.00 sec)
## 随机数函数, 取(0,100)随机值
mysql> select rand(), floor(1+rand()*99);
+--------------------+--------------------+
| rand()             | floor(1+rand()*99) |
+--------------------+--------------------+
| 0.3667208239126213 |                 50 |
+--------------------+--------------------+
1 row in set (0.00 sec)

3. 字符串类型 ​

Alt text 这里字符和字节的区别在于字符是由字符集的概念, 值得注意的是Text是支持字符集的但是底层是字节。

sh
mysql> create table t_test2(a char(1), b varchar(1), c binary(1), d varbinary(1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t_test2 values ('我','我', 'a', 'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_test2 values ('我','我', '我', '你');
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> select length('我'),char_length('我');
+---------------+--------------------+
| length('我')  | char_length('我')  |
+---------------+--------------------+
|             3 |                  1 |
+---------------+--------------------+
1 row in set (0.00 sec)

3.1 字符集 ​

常见字符集:utf8、utf8mb4、gbk，gb18030。推荐使用utf8mb4。查看mysql支持的字符集：

sh
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8mb3_general_ci  |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

可以看到utf8mb4占用最大4个字节，查看当前数据库默认的字符集：

sh
mysql> show variables like 'character%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| character_set_client     | utf8mb3                                  |
| character_set_connection | utf8mb3                                  |
| character_set_database   | utf8mb4                                  |
| character_set_filesystem | binary                                   |
| character_set_results    | utf8mb3                                  |
| character_set_server     | utf8mb4                                  |
| character_set_system     | utf8mb3                                  |
| character_sets_dir       | /opt/module/mysql-8.0.39/share/charsets/ |
+--------------------------+------------------------------------------+
8 rows in set (0.00 sec)

设置表字符集：

sql
-- 创建表指定字符集
create table 表名(字段...) charset=utf8mb4;
-- 已存在的表修改字符集，会锁表操作
alter table 表名 convert to character set utf8mb4;
-- 不建议使用, 因为它不改已存在数据的字符集
alter table 表名 character=utf8mb4;

不建议将utf8\utf8mb4转换成gbk, 因为utf8\utf8mb4保存的字符范围更大，转换成gbk会丢失数据。MySQL可以支持单独列的字符集设置，一般很少用，直接统一设置表字符集。

sh
## 将字符串或数字转换为其十六进制表示
mysql> select hex('a'), hex('我是');
+----------+---------------+
| hex('a') | hex('我是')   |
+----------+---------------+
| 61       | E68891E698AF  |
+----------+---------------+
1 row in set (0.00 sec)
## 数字转字符串
select cast(123 as char(10));

3.2 字符集排序规则 ​

主要用来比较字符串的。当前默认字符集是：

sh
mysql> select 'a'='A';
+---------+
| 'a'='A' |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

mysql> select 'a'='A         ';
+------------------+
| 'a'='A         ' |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

原因是比较逻辑是挨个字符比较，如果首个字符顺序认为相同就不比较了。

生产环境是否需要区分大小写？

结论是不需要区分大小写，需要用到大小写的场景比如用户名密码，但是我们不需要用户名很复杂，密码并不是存储的明文，不会受到影响。如果确实需要区分大小写：

sql
create table t(a varchar(300) collate utf8mb4_bin);

3.3 常用函数 ​

sh
## 转换大小写
mysql>  select upper('aBc'), lower('aBc');
+--------------+--------------+
| upper('aBc') | lower('aBc') |
+--------------+--------------+
| ABC          | abc          |
+--------------+--------------+
1 row in set (0.00 sec)
## 获取md5数据摘要
mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)
## 字符串拼接
mysql> select concat('a', 'b', 'c'), concat_ws('.', 'a', 'b', 'c');
+-----------------------+-------------------------------+
| concat('a', 'b', 'c') | concat_ws('.', 'a', 'b', 'c') |
+-----------------------+-------------------------------+
| abc                   | a.b.c                         |
+-----------------------+-------------------------------+
1 row in set (0.00 sec)
## 字符串重复
mysql> select repeat('123', 3), repeat('a', floor(1+rand()*9));
+------------------+--------------------------------+
| repeat('123', 3) | repeat('a', floor(1+rand()*9)) |
+------------------+--------------------------------+
| 123123123        | aaaa                           |
+------------------+--------------------------------+
1 row in set (0.00 sec)
## 从左边填充，补足字符串指定长度
mysql> select lpad('123', 8, '0'), rpad('aaa', 8, '-');
+---------------------+---------------------+
| lpad('123', 8, '0') | rpad('aaa', 8, '-') |
+---------------------+---------------------+
| 00000123            | aaa-----            |
+---------------------+---------------------+
1 row in set (0.00 sec)

4. Enum和Set类型 ​

它是集合类型，ENUM类型最多允许65536个值，SET类型最多允许64个值。通过sql mode参数可以用于约束检查：

sh
mysql> create table t_test4(user varchar(300), sex ENUM('男', '女'));
Query OK, 0 rows affected (0.03 sec)
mysql> set sql_mode='strict_trans_tables';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> insert into t_test4 values ('jack', 'a');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
mysql> insert into t_test4 values ('jack', '1');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t_test4;
+------+------+
| user | sex  |
+------+------+
| jack | 男   |
+------+------+
1 rows in set (0.00 sec)

5. 日期类型 ​

Alt text

sh
## 获取当前距离1970-01-01的秒数
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1742174581 |
+-----------------------+
1 row in set (0.00 sec)

5.1 常用函数 ​

sh
## 获取当前时间，可以支持毫秒
mysql> select now(), now(3);
+---------------------+-------------------------+
| now()               | now(3)                  |
+---------------------+-------------------------+
| 2025-03-17 10:28:34 | 2025-03-17 10:28:34.703 |
+---------------------+-------------------------+
1 row in set (0.00 sec)
## current_timestamp和now同义
mysql> select current_timestamp(), current_timestamp(3);
+---------------------+-------------------------+
| current_timestamp() | current_timestamp(3)    |
+---------------------+-------------------------+
| 2025-03-17 10:29:39 | 2025-03-17 10:29:39.588 |
+---------------------+-------------------------+
1 row in set (0.00 sec)
## sysdate表示sql实际开始执行时间，now表示当前时间
mysql> select sysdate(),sysdate(6), now(6);
+---------------------+----------------------------+----------------------------+
| sysdate()           | sysdate(6)                 | now(6)                     |
+---------------------+----------------------------+----------------------------+
| 2025-03-17 10:32:54 | 2025-03-17 10:32:54.289806 | 2025-03-17 10:32:54.289633 |
+---------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)
## 日期加减
mysql> select now(), date_add(now(), interval 1 day), date_sub(now(), interval 1 hour);
+---------------------+---------------------------------+----------------------------------+
| now()               | date_add(now(), interval 1 day) | date_sub(now(), interval 1 hour) |
+---------------------+---------------------------------+----------------------------------+
| 2025-03-17 10:36:34 | 2025-03-18 10:36:34             | 2025-03-17 09:36:34              |
+---------------------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)
## 格式化日期成字符串

mysql> select date_format(now(), '%Y%m%d'), date_format(now(), '%Y%m%d %H:%i:%s');
+------------------------------+---------------------------------------+
| date_format(now(), '%Y%m%d') | date_format(now(), '%Y%m%d %H:%i:%s') |
+------------------------------+---------------------------------------+
| 20250317                     | 20250317 10:38:58                     |
+------------------------------+---------------------------------------+
1 row in set (0.00 sec)

6. JSON类型 ​

MySQL5.7之后支持，可以替换以前Blob/Text类型存储Json文本。好处有以下几点：

  1. JSON数据有效性检查: BLOB类型无法在数据库层做这样的约束性检查。
  2. 查询性能的提升: 查询不需要遍历所有字符串才能找到数据。
  3. 支持部分属性索引: 通过虚拟列的功能可以对JSON中的部分数据进行索引。
sh
## 建表使用json类型
mysql> create table t_json_table(id bigint primary key, data json)engine=innodb;
Query OK, 0 rows affected (0.02 sec)
## 插入数据比较灵活
mysql> insert into t_json_table values(1, '{"name":"jack", "age":34}');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_json_table values(1, '{"age":34}');
Query OK, 1 row affected (0.01 sec)
## 插入不是json会报错
mysql> insert into t_json_table values(1, 'test');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 1 in value for column 't_json_table.data'.
## 数据查询1，json_extract函数提取字段值，但是默认有双引号，可以使用json_unquote函数
mysql> select id, json_extract(data, '$.name') quote_name, json_unquote(json_extract(data, '$.name')) name from t_json_table;
+----+------------+------+
| id | quote_name | name |
+----+------------+------+
|  1 | "jack"     | jack |
+----+------------+------+
1 row in set (0.00 sec)
## 数据查询2，使用字段名->方式，如果使用字段名->>则去掉引号
mysql> select id, data->"$.name", data->>"$.name" from t_json_table;
+----+----------------+-----------------+
| id | data->"$.name" | data->>"$.name" |
+----+----------------+-----------------+
|  1 | "jack"         | jack            |
|  2 | NULL           | NULL            |
+----+----------------+-----------------+
2 rows in set (0.01 sec)

6.1 相关函数 ​

Alt text

sh
## 创建虚拟列
mysql> alter table t_json_table add column name varchar(300)  as (json_unquote(json_extract(data, '$.name'))) virtual;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
## 查询数据
mysql> select * from t_json_table;
+----+-----------------------------+------+
| id | data                        | name |
+----+-----------------------------+------+
|  1 | {"age": 34, "name": "jack"} | jack |
|  2 | {"age": 34}                 | NULL |
+----+-----------------------------+------+
2 rows in set (0.00 sec)
## 创建索引，加速查询
mysql> alter table t_json_table add index idx_name (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0