数据类型

数据值类别

数值:

字符串值:

'cxk' -- 推荐使用单引号

二进制串比较是逐字节比较

非二进制串根据排序规则比较

日期时间值:

'2020-08-25'
'11:47:00'
'2020-08-25 11:47:00'

SELECT '2020-08-25 11:47:00' + INTERVAL 2 DAY;

空间值:(10 20)

布尔值:0会被当成假 非0非NULL会被当成真

NULL值:\N 会被当成NULL

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间,一般情况下越小的列越好

整数类型可以附带UNSIGNED属性,这可以使它的正数表示范围提高一倍。

同时,在定义数据类型时的INT(11) 这里的11指的是在交互终端下显示的字符个数,INT(1)和INT(20)在存储和计算上都是相同的

浮点数

FLOAT 和 DOUBLE 为浮点类型,DECIMAL 为高精度小数类型,DECIMAL 的计算比浮点类型需要更高的代价

字符串

一种是定长的(char),一种是变长的(varchar)。

变长类型使用额外的1个或两个字节来存储长度,能够节省空间,因为只需要存储必要的内容,但当变长类型发生UPDATE操作后,需要执行额外的操作

存储和检索时,VARCHAR 末尾的空格会保留下来,而会 CHAR 末尾的空格会被删除。

填充和删除空格的行为在所有存储引擎上都一样,这是在MySQL服务器层处理的。

另外两种类型是BLOB和TEXT,都是为了存储很大的数据而设计出来的。当这些数据很大时,一些存储引擎会将这些数据存储在外部,通过指针来引用,这两种类型不能将全部的数据用以建索引,除非不得以,否则不要使用这两种类型。

枚举

MySQL底层将枚举转换为整数来进行处理,所以枚举的性能相较于字符串快,但缺点是一旦是枚举列表发生变化,就必须通过ALTER TABLE来进行添加。

时间和日期

能够保存从 1000 年到 9999 年的日期和时间,精度为秒,时区无关

和 UNIX 时间戳相同,应该尽量使用 TIMESTAMP,因为它比 DATETIME 空间效率更高

位数据类型

谨慎使用BIT类型。SET类型类似于枚举。

序列

使用 AUTO_INCREMENT 来生成序列

自增值的行为如下:

通用

存放位置

存储引擎特有

MyISAM:

InnoDB:

需要考虑的问题

批量插入批量申请自增 id:

不使用 AUTO_INCREMENT 生成序列

UPDATE seq SET n = LAST_INSERT_ID(n+1); -- 调用有参方法 下次无参调用就会返回n+1
INSERT INTO tb(..) VALUES(LAST_INSERT_ID()...)
START TRANSACTION;
UPDATE seq  SET n = n+1;
COMMIT;

自增锁

MySQL 5.0 版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放

MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode:

MySQL 的 ID 及上限

表达式

SELECT (SELECT ...),.. FROM ...; -- 标量子查询提供单个值必须使用括号

运算符

算术:

逻辑:

位运算:

& | ^ << >>

比较运算符:

屏幕截图 2020-08-26 101539

类型转换

MySQL 会尽量将值转换成表达式所需要的类型

选择