logo头像

云影sky

mysql数据类型和运算符

MySQL 支持多种数据类型,主要有数值类型、日期、时间类型和字符串类型

  • 数值数据类型:包括整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型 FLOAT 和 DOUBLE、定点小数类型 DECIMAL。
  • 日期/时间类型:包括 YEAR、TIME、DATE、DATETIME、TIMESTAMP。
  • 字符串类型:包括 CHAR、VARCHAR、BINARY、VARBINARY、BOLB、TEXT、ENUM、SET。

整数类型

类型名 存储需求 有符号 无符号 说明
TINYINT 1字节 -128~127 0~255 很小的整数
SMALLINT 2字节 -32768~32767 0~65535 小的整数
MEDIUMINT 3字节 -8388608~8388607 0~16777215(8位) 中等大小的整数
INT 4字节 -2147483648~2147483647 0~4294967295(10位) 普通大小的整数
BIGINT 8字节 x 0~1.8E+19(20位) 大整数

注意:显示宽度和数据类型的取值范围无关。显示宽度只是指明了最大可能显示的数字个数,数值的位数小于指定的宽度时会有空格填充,如果插入了大于显示宽度的值,只要该值不超过取值范围,数值依然可以插入。

例如

1
2
3
4
5
6
7
8
> CREATE TABLE testlen ( total TINYINT(2) UNSIGNED NOT NULL );
> INSERT INTO testlen (total) values (111); # 可以正常插入数据
> SELECT * FROM testlen;
+-------+
| total |
+-------+
| 111 |
+-------+

浮点数类型和定点数类型

MySQL 使用 浮点数和定点数表示小数。浮点类型有两种:单精度浮点类型(FLOAT)和双精度浮点类型(DOUBLE)。定点类型只有一种: DECIMAL。

浮点类型和定点类型都可以用 (M,N) 来表示,期中 M 称为精度,表示总共的位数;N 称为标度,表示小数的位数。

类型名 存储需求 有符号 无符号 说明
FLOAT 4字节 -3.40E+38~-1.18E-38 0和1.18E-38~3.4E+38 单精度浮点数
DOUBLE 8字节 -1.80E+308~-2.23E-308 0和2.23E-308~1.80E+308 双精度浮点数
DECIMAL(M,D) M+2字节 x x 压缩的’严格’定点数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
> CREATE TABLE testnum
(
x FLOAT(5,1) UNSIGNED,
y DOUBLE(5,1) UNSIGNED,
z DECIMAL(5,1) UNSIGNED
);

> INSERT INTO testnum (x,y,z) VALUES (1234.5,1234.5,1234.5);
> INSERT INTO testnum (x,y,z) VALUES (1234.55,1234.55,1234.55);

+--------+--------+--------+
| x | y | z |
+--------+--------+--------+
| 1234.5 | 1234.5 | 1234.5 |
| 1234.5 | 1234.5 | 1234.6 |
+--------+--------+--------+

> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+

FLOAT 和 DOUBLE 在不指定精度时,默认会采用实际的精度(由计算机硬件和操作系统决定),DECIMAL 如不指定精度则默认为(10,0)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
> CREATE TABLE testnumm 
(
x FLOAT,
y DOUBLE,
z DECIMAL
)

> INSERT INTO testnumm (x,y,z) VALUES (123456.7890,123456.7890,123456.7890);
Query OK, 1 row affected, 1 warning (0.01 sec)

+--------+------------+--------+
| x | y | z |
+--------+------------+--------+
| 123457 | 123456.789 | 123457 |
+--------+------------+--------+

日期与时间类型

MySQL 中表示日期的数据类型,主要有 DATETIME、DATE、TIMESTAMP、TIME、YEAR。

类型名称 日期格式 日期范围 存储需求
YEAR YYYY 1901~2155 1字节
TIME HH:MM:SS -838:59:59~838:59:59 3字节
DATE YYYY-MM-DD 1000-01-01~9999-12-3 3字节
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-3123:59:59 8字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC 4字节

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个区别,DATETIME 是输入什么就存储什么,与时区无关;而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。

字符串类型

MySQL 中字符串类型有 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET。

其中 VARCHAR、BLOB、TEXT 类型是变长类型,对于其存储需求取决于列值的实际长度,而不是取决于类型的最大可能尺寸。

类型名称 说明 存储需求
CHAR(M) 固定长度非二进制字符串 M 字节,1<=M<=255
VARCHAR(M) 变长非二进制字符串 L+1 字节,在此 L<= M 和 1<= M <=255
TINYTEXT 非常小的非二进制字符串 L+1 字节,在此L < 2^8
TEXT 小的非二进制字符串 L+2 字节,在此 L < 2^16
MEDIUMTEXT 中等大小的非二进制字符串 L+3 字节,在此 L < 2^24
LONGTEXT 大的非二进制字符串 L+4 字节,在此 L < 2^32
ENUM 枚举类型,只能有一个枚举字符串值 1或2字节,取决于枚举值的数目(最大值65535)
SET 字符串对象,可以有零个或多个 SET 成员 1、2、3、4或8个字节,取决于集合成员的数量(最多64个成员)

CHAR 和 VARCHAR 类型

CHAR(M) 为固定长度字符串,在定义时要指定字符串列长。当保存时在右侧填充空格以达到指定的长度。M 表示列长度,M 的范围是 0~255 个字符。

VARCHAR(M) 是长度可变的字符串,M 表示最大列长度。M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而其实际占用的空间为字符串的实际长度加1。例如,VARCHAR(50) 定义了一个最大长度为50的字符串,如果插入的字符串只有10个字符,则实际存储的字符串为10个字符和一个字符串结束字符。在保存和检索 VARCHAR 的值时尾部的空格仍保留。

以下是一个对比的例子

插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求
‘’ ‘ ‘(4个空格) 4字节 ‘’ 1字节
‘ab’ ‘ab ‘(2个空格) 4字节 ‘ab’ 3字节
‘abc’ ‘abc ‘(1个空格) 4字节 ‘abc’ 4字节
‘abcd’ ‘abcd’ 4字节 ‘abcd’ 5字节
‘abcdef’ ‘abcd’ 4字节 ‘abcd’ 5字节

TEXT 类型

  • TINYTEXT:最大长度为255(2^8 - 1)个字符的 TEXT 列。
  • TEXT:最大长度为65535(2^16 - 1)个字符的 TEXT 列。
  • MEDIUMTEXT:最大长度为16 777 215(2^24 - 1)个字符的 TEXT 列。
  • LONGTEXT:最大长度为 4294 967 295 或 4GB(2^32 - 1)个字符的 TEXT 列。

ENUM 类型

ENUM 是一个字符串对象(只要字符串),其值为创建表时在列规定中枚举的一列值。一次只能取一个。ENUM 值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从1开始编号,MySQL 存储的就是这个编号。枚举最多有 65535 个元素。

ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL,NULL 值则为该列的一个有效值,并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL,其默认值为允许的值列表的第一个元素

SET 类型

SET 是一个字符串对象,可以有零个或多个值,SET 列最多有 64 列,其值为创建表时所规定的一列值。SET 可以从所规定的列值中选择多个字符联合插入。如果插入的字符有重复,则 MySQL 会自动删除重复的值。

二进制类型

MySQL 支持两类字符型数据:文本字符串和二进制字符串。MySQL 中的二进制数据类型有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB。

类型名称 说明 存储需求
BIT(M) 位字段类型 大约 (M+7)/ 8个字节
BINARY(M) 固定长度二进制字符串 M 个字节
VARBINARY(M) 可变长度二进制字符串 M+1 个字节
TINYBLOB(M) 非常小的 BLOB L+1 字节,在此 L < 2^8
BLOB(M) 小 BLOB L+2 字节,在此L < 2^16
MEDIUMBLOB(M) 中等大小的 BLOB L+3 字节,在此 L < 2^24
LONGBLOB(M) 非常大的 BLOB L+4 字节,在此 L < 2^32

比较运算符

比较运算符的结果总是1、0或者 NULL,比较运算符经常在 SELECT 的查询条件子句中使用

运算符 作用
= 等于
<=> 安全等于
<>(!=) 不等于
<= 小于等于
>= 大于等于
> 大于
IS NULL 判断一个值是否为 NULL
IS NOT NULL 判断一个值是否不为 NULL
LEAST 在有两个或多个参数时,返回最小值
GREATEST 在有两个或多个参数时,返回最大值
BETWEEN AND 判断一个值是否落在两个值之间
ISNULL 与 IS NULL 的作用相同
IN 判断一个值时 IN 列表中的值
NOT IN 判断一个值不是 IN 列表中的值
LIKE 通配符匹配
REGEXP 正则表达式匹配
LIKE 运算符在匹配时,可以使用
% ,匹配任意数目的字符,包括零字符
_,只能匹配一个字符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
> SELECT NULL IS NULL, ISNULL(NULL), ISNULL(10),10 IS NOT NULL;
+--------------+--------------+------------+----------------+
| NULL IS NULL | ISNULL(NULL) | ISNULL(10) | 10 IS NOT NULL |
+--------------+--------------+------------+----------------+
| 1 | 1 | 0 | 1 |
+--------------+--------------+------------+----------------+

> SELECT 4 BETWEEN 4 AND 6, 12 BETWEEN 9 AND 15, 'X' BETWEEN 'W' AND 'Z';
+-------------------+---------------------+-------------------------+
| 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 15 | 'X' BETWEEN 'W' AND 'Z' |
+-------------------+---------------------+-------------------------+
| 1 | 1 | 1 |
+-------------------+---------------------+-------------------------+

> SELECT LEAST(2,0),LEAST(20.0,3.0,100), LEAST('A', 'B', 'C');
+------------+---------------------+----------------------+
| LEAST(2,0) | LEAST(20.0,3.0,100) | LEAST('A', 'B', 'C') |
+------------+---------------------+----------------------+
| 0 | 3.0 | A |
+------------+---------------------+----------------------+

> SELECT GREATEST(2,0),GREATEST(20.0,3.0,100), GREATEST('A', 'B', 'C');
+---------------+------------------------+-------------------------+
| GREATEST(2,0) | GREATEST(20.0,3.0,100) | GREATEST('A', 'B', 'C') |
+---------------+------------------------+-------------------------+
| 2 | 100.0 | C |
+---------------+------------------------+-------------------------+

> SELECT 2 IN (1,3,5, 'THIS'), 'THIS' IN (1,2,3,'THIS');
+----------------------+--------------------------+
| 2 IN (1,3,5, 'THIS') | 'THIS' IN (1,2,3,'THIS') |
+----------------------+--------------------------+
| 0 | 1 |
+----------------------+--------------------------+

> SELECT 2 IN (1,3,5, 'THIS', NULL), NULL IN (1,2,3,'THIS', NULL);
+----------------------------+------------------------------+
| 2 IN (1,3,5, 'THIS', NULL) | NULL IN (1,2,3,'THIS', NULL) |
+----------------------------+------------------------------+
| NULL | NULL |
+----------------------------+------------------------------+

> SELECT 'AB' LIKE 'AB__', 'AB' LIKE 'AB%';
+------------------+-----------------+
| 'AB' LIKE 'AB__' | 'AB' LIKE 'AB%' |
+------------------+-----------------+
| 0 | 1 |
+------------------+-----------------+

逻辑运算符

在 MySQL 中,他们体现为 1(TRUE),0(FALSE),NULL

运算符 作用
NOT 或者 ! 逻辑非
AND 或者 && 逻辑与
OR 或者 || 逻辑或
XOR 逻辑异或