mysql cheat sheet

mysql
16 minutes read (About 2400 words)

-

数据类型

整型

数据类型 字节数 带符号最小值 带符号最大值 不带符号最小值 不带符号最大值
TINYINT 1 -128 127 0 255
SMALLINT 2 -32768 32767 0 65535
MEDIUMINT 3 -8388608 8388607 0 16777215
INT 4 -2147483648 2147483647 0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807 0 18446744073709551616

整型（N）形式

  • 无论 N 等于多少，int 永远占 4 个字节
  • N 表示的是显示宽度，不足的用 0 补足，超过的无视长度而直接显示整个数字，但这要整型设置了 unsigned zerofill 才有效

浮点型

数据类型 字节数 备注
float 4 单精度浮点型
double 8 双精度浮点型

定点型

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
DROP TABLE IF EXISTS test_decimal;

CREATE TABLE test_decimal (
	float_num float(10, 2),
	double_num double(20, 2),
	decimal_num decimal(20, 2)
) ENGINE = innodb CHARSET = utf8;

INSERT INTO
	test_decimal
VALUES
	(
		1234567.66,
		1234567899000000.66,
		1234567899000000.66
	);

INSERT INTO
	test_decimal
VALUES
	(
		1234567.66,
		12345678990000000.66,
		12345678990000000.66
	);
  • float/double 在 db 中存储的是近似值，而 decimal 则是以字符串形式进行保存的
  • decimal(M,D)的规则和 float/double 相同，但区别在 float/double 在不指定 M、D 时默认按照实际精度来处理而 decimal 在不指定 M、D 时默认为 decimal(10, 0)

日期类型

数据类型 字节数 格式 备注
date 3 yyyy-MM-dd 存储日期值
time 3 HH:mm:ss 存储时分秒
year 1 yyyy 存储年
datetime 8 yyyy-MM-dd HH:mm:ss 存储日期+ 时间
timestamp 4 yyyy-MM-dd HH:mm:ss 存储日期+ 时间，可作时间戳 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TABLE IF EXISTS test_time;

CREATE TABLE test_time (
	date_value date,
	time_value time,
	year_value year,
	datetime_value datetime,
	timestamp_value timestamp
) ENGINE = innodb CHARSET = utf8;

INSERT INTO
	test_time
VALUES
(NOW(), NOW(), NOW(), NOW(), NOW());

  • datetime 占 8 个字节，timestamp 占 4 个字节

  • 由于大小的区别，datetime 与 timestamp 能存储的时间范围也不同，datetime 的存储范围为 1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp 存储的时间范围为 19700101080001——20380119111407

  • datetime 默认值为空，当插入的值为 null 时，该列的值就是 null；timestamp 默认值不为空，当插入的值为 null 的时候，mysql 会取当前时间

  • datetime 存储的时间与时区无关，timestamp 存储的时间及显示的时间都依赖于当前时区

  • 在实际工作中，一张表往往我们会有两个默认字段，一个记录创建时间而另一个记录最新一次的更新时间，这种时候可以使用 timestamp 类型来实现：

    1
    2
    create_at timestamp DEFAULT CURRENT_TIMESTAMP COMMENT "创建时间",
    update_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT "修改时间"

字符串类型

char、varchar、text、blob

它们的存储方式和数据的检索方式都不一样.数据的检索效率是：char > varchar > text.空间占用方面，就要具体情况具体分析了。

  • char：存储定长数据很方便，CHAR 字段上的索引效率级高，必须在括号里定义长度，可以有默认值，比如定义 char(10)，那么不论你存储的数据是否达到了 10 个字符，都要占去 10 个字符的空间（自动用空格填充），且在检索的时候后面的空格会隐藏掉，所以检索出来的数据需要记得用什么 trim 之类的函数去过滤空格。
  • varchar：存储变长数据，但存储效率没有 CHAR 高，必须在括号里定义长度，可以有默认值。保存数据的时候，不进行空格自动填充，而且如果数据存在空格时，当值保存和检索时尾部的空格仍会保留。另外，varchar 类型的实际长度是它的值的实际长度+1，这一个字节用于保存实际使用了多大的长度。
  • text：存储可变长度的非 Unicode 数据，最大长度为 2^31-1 个字符。text 列不能有默认值，存储或检索过程中，不存在大小写转换，后面如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是你插入数据的时候，超过你指定的长度还是可以正常插入。

关于存储空间：

在使用 UTF8 字符集的时候，手册上是这样描叙的：

  • 基本拉丁字母、数字和标点符号使用一个字节；
  • 大多数的欧洲和中东手写字母适合两个字节序列：扩展的拉丁字母（包括发音符号、长音符号、重音符号、低音符号和其它音符）、西里尔字母、希腊语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语和其它语言；
  • 韩语、中文和日本象形文字使用三个字节序列。

结论：

  • char(M),varchar(M) M 表示字符长度
  • 经常变化的字段用 varchar；
  • 知道固定长度的用 char；
  • 尽量用 varchar；
  • 超过 255 字节的只能用 varchar 或者 text；
  • 能用 varchar 的地方不用 text；
  • 能够用数字类型的字段尽量选择数字类型而不用字符串类型的（电话号码），这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

官网

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data Type Storage Required
CHAR(M) The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M) M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT L + 1 bytes, where L < 28
BLOB, TEXT L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT L + 4 bytes, where L < 232
ENUM(‘value1’,’value2’,…) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET(‘value1’,’value2’,…) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

日期处理

  • SELECT now(); - 当前日期时间
  • SELECT CURDATE() - 当前日期
  • SELECT CURTIME() - 当前时间

日期时间选取函数

  • DATE('2017-05-15 10:37:14.123456') - 获取日期：2017-05-15
  • TIME('2017-05-15 10:37:14.123456') - 获取时间：10:37:14.123456
  • YEAR('2017-05-15 10:37:14.123456') - 获取年份
  • MONTH('2017-05-15 10:37:14.123456') - 获取月份
  • DAY('2017-05-15 10:37:14.123456') - 获取日
  • HOUR('2017-05-15 10:37:14.123456') - 获取时
  • MINUTE('2017-05-15 10:37:14.123456') - 获取分
  • SECOND('2017-05-15 10:37:14.123456') - 获取秒
  • MICROSECOND('2017-05-15 10:37:14.123456') - 获取毫秒
  • QUARTER('2017-05-15 10:37:14.123456') - 获取季度
  • WEEK('2017-05-15 10:37:14.123456') - 获取周
  • DAYOFYEAR('2017-05-15 10:37:14.123456') - 日期在年度中第几天
  • DAYOFMONTH('2017-05-15 10:37:14.123456') - 日期在月度中第几天
  • DAYOFWEEK('2017-05-15 10:37:14.123456') - 日期在周中第几天；周日为第一天

日期计算

  • SELECT LAST_DAY('2016-05-01') - 月份中最后一天：2016-05-31
  • DATE_ADD(date,INTERVAL expr type) - 从日期加上指定的时间间隔
  • DATE_SUB(date,INTERVAL expr type) - 从日期减去指定的时间间隔
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
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 YEAR);

-- 表示：2018-05-15 10:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 QUARTER);

-- 表示：2017-08-15 10:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 MONTH);

-- 表示：2017-06-15 10:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 WEEK);

-- 表示：2017-05-22 10:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 DAY);

-- 表示：2017-05-16 10:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 HOUR);

-- 表示：2017-05-15 11:37:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 MINUTE);

-- 表示：2017-05-15 10:38:14.123456
SELECT
	DATE_ADD('2017-05-15 10:37:14.123456', INTERVAL 1 SECOND);

-- 表示：2017-05-15 10:37:15.123456
SELECT
	DATE_ADD(
		'2017-05-15 10:37:14.123456',
		INTERVAL 1 MICROSECOND
	);

日期转换函数

  • TIME_TO_SEC(‘01:00:05’); - 3605
  • TO_DAYS(‘2017-06-05’); - 736850
  • STR_TO_DATE(‘06.05.2017 19:40:30’, ‘%m.%d.%Y %H:%i:%s’);- 2017-06-05 19:40:30
  • DATE_FORMAT(‘2017-05-12 17:03:51’, ‘%Y 年%m 月%d 日 %H 时%i 分%s 秒’);- 2017 年 05 月 12 日 17 时 03 分 51 秒(具体需要什么格式的数据根据实际情况来;小写 h 为 12 小时制;)

插入

获取插入记录的自增长 ID

1
public void insert(Role role);

sql

1
2
3
4
5
6
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO
            role(role_name, role_name_zh)
        VALUES
            (#{roleName}, #{roleNameZh})
    </insert>

获取自增

插入记录后，传入的参数role中的相应的属性id已经被设置为该记录在数据库中的 id 了。因此直接使用role.getId()即可获取记录 id。

–bb

引擎

  • INNODB
  • MYISAM

对比

  • InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
  • InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
  • InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的
  • InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
  • InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一

如何选择

  • 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM
  • 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用 InnoDB
  • 系统奔溃后，MyISAM 恢复起来更困难，能否接受，不能接受就选 InnoDB
  • MySQL5.5 版本开始 Innodb 已经成为 Mysql 的默认引擎(之前是 MyISAM)
  mysql
vscode
springboot cookie
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×