MySQL 数据类型和运算符

Question

1、MYSQL 数据类型介绍

数据类型主要有下面几种

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

• 数值数据类型: 包括整数类型 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT；浮点小数数据类型 FLOAT 和 DOUBLE；定点小数类型 DECIMAL 。
• 日期/时间类型: 包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
• 字符串类型: 包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。字符串类型又分为文本字符串和二进制字符串。

1)、整数类型

整数数据类型主要有一下几种：

不同的数据类型取值范围如下：

注意 INT ( num ) 中的数和取值范围无关。

举例

create table tb_emp1{
	id INT(11),
	name VARCHAR(25),
	deptId INT(11),
	salary FLOAT
};

id 字段的数据类型为 INT(11)，注意到后面的数字 11，这表示的是该数据类型指定的显示宽度，指定能够显示的数值中数字的个数。例如，假设声明一个 INT 类型的字段: year INT(4) 该声明指明，在 year 字段中的数据一般只显示 4 位数字的宽度。在这里要注意: 显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小鱼指定的宽度时会由空格填充。

2)、浮点数类型和定点数类型

小数类型：

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL(M, D), DEC	压缩的"严格"定点数	M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE，DECIMAL 实际是以串存放的，DECIMAL 可能的最大取值范围与 DOUBLE 一样，但是其有效的取值范围由 M 和 D 的值决定。如果改变 M 而固定 D，则其取值范围将随 M 的变大而变大。从表中可以看到，DECIMAL 的存储空间并不是固定的，而由其精度值 M 决定，占用 M+2 个字节。

FLOAT 类型的取值范围如下：

• 有符号的取值范围: -3.402823466E+38 ~ -1.175494351E-38 。
• 无符号的取值范围: 0 和 1.175494351E-38 ~ 3.402823466E+38 。

DOUBLE 类型的取值范围如下：

• 有符号的取值范围: -1.7976931348623157E+308 ~ -2.2250738585072014E-308 。
• 无符号的取值范围: 0 和 2.2250738585072014E-308 ~ 1.7976931348623157E+308 。

注意: 不论定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

注意浮点数和定点数的使用场合：

• 在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候〈如货币，科学数据等) 使用 DECIMAL 的类型比较好；
• 另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点型时需要注意，并尽量避免做浮点数比较。

3)、时间和日期类型

a)、Year

举几个例子：

• 0 表示 0000 ， ‘0’ 和 ‘00’ 表示 2000 ；
• ‘78’ 和 78 表示 1978 ， ‘68’ 和 68 表示 2068；

b)、Time

案例：

create table tmp4(t Time);
delete from tmp4;
insert into tmp4 values('10:05:05'),('23:23'),('2 10:10'),('3 02'),('10'),(now()),(current_time);

效果

c)、Date

create table tmp5(d Date);
insert into tmp5 values('1998-09-01'),('19980902'),('980903'),(19980904),(980905),(100906),(000907),(current_date);

效果

d)、DateTime

举例：

create table tmp6(dt DateTime);
insert into tmp6 values('1998-08-08 08:08:08'),('19980809080808'),('98-08-08 08:08:08'),('980808080808'),(19980808080808),(980808080808);

效果

e)、TimeStamp

TimeStamp 把时区修改之后查询结果就会不同，但是 DateTime 不会。

4)、文本字符串类型

a)、 char 和 varchar 类型

char 数据类型长度不可变， varchar 长度可变

举例：

create table tmp8(ch char(4),vch varchar(4));
insert into tmp8 values('ab  ','ab  ');-- 注意这里有空格
select concat('(',ch,')'),concat('(',vch,')') from tmp8;

看效果 vch 中的空格没有被截取（即 ch 末尾的两个空格被删除了，而 vch 的没有删除)

b)、Text 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。Text 类型分为 4 种: TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

• TINYTEXT 最大长度为 255(2⁸ - 1) 字符的 TEXT 列；
• TEXT 最大长度为 65535(2¹⁶ - 1) 字符的 TEXT 列；
• MEDIUMTEXT 最大长度为 16777215(2²⁴ - 1) 字符的 TEXT 列；
• LONGTEXT 最大长度为 4294967295 或 4GB(2³² - 1) 字符的 TEXT 列；

c)、 Enum 类型

ENUM 是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法格式如下:''

字段名 ENUM('值 1', '值 2', ..., '值 n')

字段名指将要定义的字段，值 n 指枚举列表中的第 n 个值。 ENUM 类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动被删除。ENUM 值在内部用整数表示，每个枚举值均有一个索引值: 列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号。枚举最多可以有 65 535 个元素。

例如定义 ENUM 类型的列（first、second、third），该列可以取的值和每个值的索引如表所示：

举例：

create table tmp9(enm Enum('first','second','third'));
insert into tmp9 values('first'),('second'),('third'),(null);
select enm,enm+0 from tmp9;

再看一个实例

create table tmp10(soc int ,level enum('excellent','good','bad'));
insert into tmp10 values(70,'good'),(90,1),(75,2),(50,3); #'excellent','good','bad'-->对应 1，2，3
select soc,level,level+0 from tmp10;
insert into tmp10 values(100,4); #没有 4 这个选项

效果

d)、Set 类型

SET 是一个字符串对象，可以有零或多个值，SET 列最多可以有 64 个成员，其值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号 (, ) 间隔开。语法格式如下：

SET('值 1', '值 2', ... '值 n')

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每一个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动被删除。但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

如果插入 SET 字段中列值有重复，则 MySQL 自动删除重复的值，插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，并给出警告。

-- 自动排序去重
create table tmp11(s set('a','b','c','d'));  # 只能插入 a,b,c,d 这四个值
insert into tmp11 values('a'),('a,b,a'),('c,a,d');
select *from tmp11;

效果

---

5)、二进制字符串类型

a)、Bit 类型

保存的是数的二进制表示：

BIT 类型是位字段类型。M 表示每个值的位数，范围为 1-64。如果 M 被省略，默认为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b'101' ，其效果与分配 b'000101' 相同。BIT 数据类型用来保存位字段值，例如: 以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

#bit
create table tmp12(b bit(4));
insert into tmp12 values(2),(9),(15);
insert into tmp12 values(16);#报错，只能存到 0-15
select b,b+0 from tmp12;

效果

b)、Binary 和 varBinary

BINARY 类型的长度是固定的 指定长度之后 不足最大长度的 将在它们右边填充 "\0" 补齐以达到指定长度。例如: 指定列数据类型为 BINARY(3)，当插入“a”时，存储的内容实际为 “a\0\0” ，当插入 “ab” 时，实际存储的内容为 “ab\0” ，不管存储的内容是否达到指定的长度，其存储空间均为指定的值 M。

VARBINARY 类型的长度是可变的，指定好长度之后，其长度可以在 0 到最大值之间。例如: 指定列数据类型为 VARBINARY(20)，如果插入的值的长度只有 10，则实际存储空间为 10 加 1，即其实际占用的空间为字符串的实际长度加 1。

#binary 和 varbinary
create table tmp13(b binary(3),vb varbinary(30));
insert into tmp13 values(5,5);
select length(b),length(vb) from tmp13;

效果如图：

c)、Blob 类型

BLOB 是一个二进制大对象，用来存储可变数量的数据。有四种类型: TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

BLOB 列存储的是二进制字符串（字节字符串)，TEXT 存储的是非二进制字符串（字符字符串)。

---

2、如何选择数据类型

1)、整数和浮点数

如果不需要小数部分，则使用整数来保存数据；

如果需要表示小数部分，则使用浮点数类型。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。 例如，如果列的值的范围为 1-99999, 若使用整数，则 MEDIUMINT UNSIGNED 是最好的类型，若需要存储小数，则使用 FLOAT 类型。

浮点类型包括 FLOAT 和 DOUBLE 类型。DOUBLE 类型精度比 FLOAT 类型高，因此，如要求存储精度较高时，应选择 DOUBLE 类型。

2)、浮点数和定点数

浮点数 FLOAT、DOUBLE 相对于定点数 DECIMAL 的优势是: 在长度一定的情况下, 浮点数能表示更大的数据范围，但是由于浮点数容易产生误差。

因此对精确度要求比较高时，建议使用 DECIMAL 来存储。DECIMAL 在 MySQL 中是以字符串存储的，用于定义货币等对精确度要求较高的数据。另外两个浮点数进行减法和比较运算时也容易出问题，因此在进行计算的时候，一定要小心。如果进行数值比较，最好使用 DECIMAL 类型。

3)、日期和时间类型

MySQL 对于不同种类的日期和时间有很多的数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可； 如果只记录时间，只需使用 TIME 类型。

如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储范围较大的日期最好使用 DATETIME。

TIMESTAMP 也有一个 DATETIME 不具备的属性。默认的情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录同时插入当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

4)、char 和 varchar

char 和 varchar 的区别：

• char 是固定长度字符，varchar 是可变长度字符；
• CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。

CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR 类型来实现。

存储引擎对于选择 CHAR 和 VARCHAR 的影响：

• 对于 MYyISAM 存储引擎: 最好使用固定长度( char ) 的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。
• 对于 InnoDB 存储引擎: 使用可变长度( varchar ) 的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 IO 和数据存储总量比较好。

5)、ENUM 和 SET

ENUM 只能取单值, 它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535 个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。比如: 性别字段适合定义为 ENUM 类型，每次只能从“男”或“女”中取一个值。

SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET 值。在需要取多个值的时候，适合使用 SET 类型，比如: 要存储一个人兴趣爱好，最好使用 SET 类型 。

ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。

6)、BLOB 和 TEXT

BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

---

3、常见运算符介绍

1)、运算符概述

总共有四大类：

1. 算术运算符

算术运算符用于各类数值运算，包括加 (+) 、减 (-) 、乘 (+) 、除 (/) 、求余（或称模运算，%) 。

2. 比较运算符

比较运算符用于比较运算。包括大于 (>) 、小于 (<) 、等于 (=) 、大于等于 (>=) 、小于等于 (<=) 、不等于 (!=) ，以及 IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP 等。

3. 逻辑运算符

逻辑运算符的求值所得结果均为 1 (TRUE) 、0 (FALSE) ，这类运算符有逻辑非 (NOT 或者!) 、逻辑与 (AND 或者&&) 、逻辑或 (OR 或者|) 、逻辑异或 C(XOR) 。

4. 位操作运算符

位操作运算符参与运算的操作数按二进制位进行运算。包括位与(&) 、位或 (|) 、位非(~) 、位异或 (^) 、左移 (<<) 、右移 (>>) 6 种。

2)、算数运算符

没啥好说的就是 +、－、*、/、% 。

3)、比较运算符

注意一下比较运算符

数值比较有如下规则：

• 若有一个或两个参数为 NULL，则比较运算的结果为 NULL；
• 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较；
• 若两个参数均为整数，则按照整数进行比较；
• 若一个字符串和数字进行相等判断，则 MySQL 可以自动将字符串转换为数字；

安全等于运算符

这个操作符和=操作符执行相同的比较操作，不过 <=> 可以用来判断 NULL 值。在两个操作数均为 NULL 时，其返回值为 1 而不为 NULL；而当一个操作数为 NULL 时，其返回值为 0 而不为 NULL。

<=> 在执行比较操作时和 "=" 的作用是相似的，唯一的区别是 <=> 可以来对 NULL 进行判断，两者都为 NULL 时返回 1 。

不等于运算符 <> 或者 !=：

"<>" 或者 "!=" 用于判断数字、字符串、表达式不相等的判断。如果不相等，返回值为 1； 否则返回值为 0。这两个运算符不能用于判断空值 NULL。

LEAST 运算符

语法格式为：

LEAST(值 1,值 2…,值 m)

其中值 n 表示参数列表中有 n 个值。在有两个或多个参数的情况下, 返回最小值。假如任意一个自变量为 NULL,则 LEAST() 的返回值为 NULL 。

GREATEST

语法格式： GREATEST(值 1, 值 2, 值 3) ，其中 n 表示参数列表中有 n 个值。当有 2 个或多个参数时，返回为最大值，假如任意一个自变量为 NULL，则 GREATEST() 的返回值为 NULL。\

LIKE

正则表达式 REGEXP

看一个例子

select 'ssky' regexp '^s','ssky' regexp 'y$', 'ssky' regexp '.sky', 'ssky' regexp '[ab]';

效果

s

4)、逻辑运算符

和高级语言差不多，不赘述。

5)、位运算

和高级语言差不多，不赘述。

6)、运算符优先级

4、综合案例-运算符的使用

create table tmp15(note varchar(100),price int);
insert into tmp15 values("Thisisgood",50);
#算术运算符
select price,price+10,price-10,price*2,price/2,price%3 from tmp15;
#比较运算符
select price,price>10,price<10,price != 10,price = 10,price <=>10,price <>10 from tmp15;
# in, greatest 等
select price,price between 30 and 80,greatest(price,70,30),price in(10,20,50,35) from tmp15;
# 正则等
select note,note is null,note like 't%',note regexp '$y',note regexp '[gm]' from tmp15;
# 逻辑运算
select price,price&2,price|4, ~price from tmp15;
# 位运算
select price,price<<2,price>>2 from tmp15;