首页

MySQL 的数据类型和运算符

— datatype —

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

References: 刘增杰、张少军《MySQL 5.5 从零开始学》

http://forums.mysql.com/

运行的例子

下面的实验均基于下面的数据库模式. 关于原始数据库的建立或导入见 practice_1.html

Beers(name, manf)
Bars(name, addr, license)
Drinkers(name, addr, phone)
Likes(drinker, beer)
Sells(bar, beer, price)
Frequents(drinker, bar)

加下划线的是组成键的属性.

三大类型

数值数据类型:
- 整数类型: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
- 浮点小数类型: FLOAT, DOUBLE
- 定点小数类型: DECIMAL
日期/时间类型: YEAR, TIME, DATE, DATETIME, TIMESTAMP
字符串类型: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

Numeric data types

数值数据类型

MySQL 使用标准的 ANSI SQL 数值数据类型.

INT
TINYINT
SMALLINT
MEDIUMINT
BIGINT
FLOAT(M,D)
DOUBLE(M,D)
DECIMAL(M,D)

Date and Time Type

日期/时间类型

DATE
DATETIME
TIMESTAMP
TIME
YEAR(M)

MySQL 显示 DATE 类型的数据为 'YYYY-MM-DD' 格式. 表示范围为 '1000-01-01' 至 '9999-12-31'

String Types

字符串类型

CHAR(M)
VARCHAR(M)
BLOB or TEXT
TINYBLOB or TINYTEXT
MEDIUMBLOB or MEDIUMTEXT
LONGBLOB or LONGTEXT
ENUM

布尔值类型

BOOL 和 BOOLEAN 实际上就是 TINYINT(1). 0 被认为是 false, 非零值被认为是 true.

mysql> SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
mysql> SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
mysql> SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+

TRUE 和 FALSE 分别是 1 和 0 的别称.

mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+

注意 2=FALSE 返回 false, 因为这比较的是 2=0, 当然是不相等的. 如果希望判断 2 是否为真, 应该使用下面的语句:

mysql> select if(2 is true, 'T', 'F');
+-------------------------+
| if(2 is true, 'T', 'F') |
+-------------------------+
| T                       |
+-------------------------+

请练习 IF 函数: SELECT IF( , , )

mysql> select if(0=false, 'T', 'F');
+-----------------------+
| if(0=false, 'T', 'F') |
+-----------------------+
| T                     |
+-----------------------+

注意 MySQL 中的比较运算符不是 ==, 就是单个的等号.

注: 以上实验的内容参考自 MySQL 5.5 手册.

整数类型的默认显示宽度

整数类型的显示宽度

mysql> use test;

mysql> CREATE TABLE tmp1(
    -> x TINYINT,
    -> y SMALLINT,
    -> z MEDIUMINT,
    -> m INT,
    -> n BIGINT
    -> );

mysql> desc tmp1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x     | tinyint(4)   | YES  |     | NULL    |       |
| y     | smallint(6)  | YES  |     | NULL    |       |
| z     | mediumint(9) | YES  |     | NULL    |       |
| m     | int(11)      | YES  |     | NULL    |       |
| n     | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除数据

delete from alg_users where username='horny';

JSON 类型

MySQL 5.7.8 引入了 JSON 类型

JSON(JavaScript Object Notation)

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
);

mysql> INSERT INTO customers(custinfo) VALUE
    -> ('{"name": "ZhangSan", "age": "23" ,"from": "Yangzhou"}');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM customers\G
*************************** 1. row ***************************
      id: 1
modified: 2021-04-27 10:22:38
custinfo: {"age": "23", "from": "Yangzhou", "name": "ZhangSan"}
1 row in set (0.00 sec)

可见这里的 JSON 数据, 内部是自动排序存储的.

使用 MySQL 原生 JSON 数据类型的优势

存储上类似text,可以存非常大的数据。
自动验证存储在 JSON 列中的 JSON 文档。无效文档会产生错误。
优化的存储格式。存储在 JSON 列中的 JSON 文档会被转换为一种内部格式，以允许快速读取文档元素。当服务器后期必须读取以这种二进制格式存储的 JSON 值时，不需要从文本表示中解析该值。二进制格式被结构化以使得服务器能够通过键(key)或数组索引直接查找子对象或嵌套值，而无需在文档中读取它们之前或之后的所有值。
支持索引：通过虚拟列的功能可以对JSON中部分的数据进行索引。

JSON 列所能存储的文档大小上限

JSON 列所能存储的最大 JSON 文档的大小受下面名为 max_allowed_packet 的系统变量所限.

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              1048576 |
+----------------------+
1 row in set (0.02 sec)

当服务器在内存中内部操作 JSON 值时, 它可能比此更大. 但当服务器存储 JSON 类型的值到 JSON 列时, 该限制将会起作用.

使用 JSON_STORAGE_SIZE() 函数可以返回 JSON 列所存储内容的大小.

mysql> select json_storage_size(customers.custinfo) from customers;
+---------------------------------------+
| json_storage_size(customers.custinfo) |
+---------------------------------------+
|                                    58 |
+---------------------------------------+
1 row in set (0.01 sec)

这里 58 的单位是字节(Bytes). 字符串 {"age": "23", "from": "Yangzhou", "name": "ZhangSan"} 实际的长度是 53.

mysql> SET @j='{"age": "23", "from": "Yangzhou", "name": "ZhangSan"}';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @j, LENGTH(@j) AS size;
+-------------------------------------------------------+------+
| @j                                                    | size |
+-------------------------------------------------------+------+
| {"age": "23", "from": "Yangzhou", "name": "ZhangSan"} |   53 |
+-------------------------------------------------------+------+
1 row in set (0.00 sec)

向量值索引

注: MySQL 5.7 尚不支持向量值索引(或多值索引, A multi-valued index).

mysql> CREATE TABLE customers (
    ->     id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->     custinfo JSON,
    ->     INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
    ->     );
ERROR 1064 (42000): Erreur de syntaxe près de '(CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
    )' à la ligne 5

参考文献

这里的代码来自 MySQL manual 8.0.

https://dev.mysql.com/doc/refman/5.7/en/json.html

数学运算

注意 div 和 / 的区别.

mysql> select 7 div 3, 7/3;
+---------+--------+
| 7 div 3 | 7/3    |
+---------+--------+
|       2 | 2.3333 |
+---------+--------+
1 row in set (0.00 sec)

End

Thanks very much!

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

运行的例子

三大类型

Numeric data types

数值数据类型

Date and Time Type

日期/时间类型

String Types

字符串类型

布尔值类型

布尔值类型

请练习 IF 函数: SELECT IF( , , )

整数类型的默认显示宽度

整数类型的显示宽度

删除数据

删除数据

JSON 类型

MySQL 5.7.8 引入了 JSON 类型

使用 MySQL 原生 JSON 数据类型的优势

JSON 列所能存储的文档大小上限

向量值索引

参考文献

数学运算

数学运算

End