MySQL 笔记

FIND_IN_SET

1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS find_like_example;
CREATE TABLE example_one (
	id BIGINT PRIMARY KEY,
	`names` VARCHAR(100)
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO find_like_example VALUES(1, '1,2,3,4');
INSERT INTO find_like_example VALUES(2, '3,4,5,6');

FIND_IN_SET(str, strlist) strlist 由 , 分隔，FIND_IN_SET 返回 str 在 strlist 首次出现的位置，从 1 开始
列子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT FIND_IN_SET('1', '1,2,3,4');
FIND_IN_SET('1', '1,2,3,4')  
-----------------------------
                            1

SELECT FIND_IN_SET('2', '1,2,3,4');

FIND_IN_SET('2', '1,2,3,4')  
-----------------------------
                            2

SELECT FIND_IN_SET('0', '1,2,3,4');
FIND_IN_SET('0', '1,2,3,4')  
-----------------------------
                            0

类型转换

MySQL CAST与 CONVERT 函数的用法
Type Conversion

显式转换

CAST(value AS type)
CONVERT(value, type)
二进制，同带binary前缀的效果：BINARY
字符型，可带参数：CHAR()
日期：DATE
时间：TIME
日期时间型：DATETIME
浮点数：DECIMAL
整数：SIGNED
无符号整数：UNSIGNED

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT CONVERT('1111',SIGNED);
CONVERT('1111',SIGNED)  
------------------------
                    1111

SELECT CAST('125hello.123' AS SIGNED);
CAST('125hello.123' AS signed)  
--------------------------------
                             125

SELECT CONVERT(123456, CHAR(3));
convert(123456, Char(3))  
--------------------------
123

比较操作

如果其中有一个参数为 NULL，那么比较的结果为 NULL。使用 NULL-safe <=> 比较 NULL <=> NULL 时，返回值为 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 1 = NULL;
1 = null  
----------
    (NULL)

SELECT NULL = NULL;
null = null  
-------------
       (NULL)

SELECT NULL <=> NULL;
null <=> null  
---------------
              1

如果参数都是字符串，那么他们做字符串比较
如果参数都是整数，那么他们按整数方式比较
如果十六进制字符串不和数字比较，那么他们将被视为二进制字符串
如果有一个参数是 TIMESTAMP 或 DATETIME，其余的参数是常数，那么这个常数会被转换为时间戳再进行比较

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed.
This is done to be more ODBC-friendly. This is not done for the arguments to IN().
To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.

其它的情况，按浮点数比较

1
2
3
4
SELECT '123hello' = 123.0;
'123hello' = 123.0  
--------------------
                   1

日期和时间类型的比较

  • TIMESTAMP 不能比 1970 UTC 早和比 2038-01-19 03:14:07 UTC 晚。1968-01-01 是一个合法的 DATA 或 DATATIME 值，但是不是一个合法的 TIMESTAMP 值，会被转换为 0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    DROP TABLE IF EXISTS timestamp_example;
    CREATE TABLE timestamp_example(
    	create_time TIMESTAMP
    ) ENGINE = INNODB DEFAULT CHARSET utf8mb4;
    INSERT INTO timestamp_example VALUE('1968-01-01');
    SELECT * FROM timestamp_example;

            create_time  
    ---------------------
      0000-00-00 00:00:00

    SELECT TIMESTAMP('1968-01-01');
    timestamp('1968-01-01')  
    -------------------------
    1968-01-01 00:00:00

DATE 转换为 DATATIME、TIMESTAMP、TIME
DATE 转换为 DATATIME、TIMESTAMP 时会添加时间部分 00:00:00
DATA 转换为 TIME时，结果为 00:00:00

  • DATETIME、TIMESTAMP的转换：

5.5 版本，转换为 DATE 时，会丢弃时间部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS datatime_example;

CREATE TABLE datatime_example(
	create_time DATE
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO datatime_example VALUE('1999-12-31 23:59:59.499');
INSERT INTO datatime_example VALUE('1999-12-31 23:59:59.500');

SELECT * FROM datatime_example;

create_time  
-------------
1999-12-31   
1999-12-31

5.5 以上不包括 5.5 版本 转换为 DATE 时，会四舍五入小数部分

1
2
3
4
create_time  
-------------
1999-12-31   
2000-01-01

转换为 TIME 时会丢掉日期部分

  • 字符串和数字
    YYYY-MM-DD 或 YY-MM-DD形式的字符串，不必须 - 做分隔符，在日期部分所有的标点符号做分隔符都是合法的。2012-12-31 2012/12/31 2012^12^31 2012@12@31 是相同的
    没有分隔符的字符串 YYYYMMDD YYMMDD 将被视为 DATE。20070523 和 070523 被解释为 2007-05-23。071332是不合法的，月份不合法将被解释为 0000-00-00
    数字 YYYYMMDD 或 YYMMDD 可以作为 DATE. 19830905 和 830905 被解释为 1983-09-05

YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 形式的字符串，不必须用 :做分隔符，在日期和时间部分所有符号都是合法的。2012-12-31 11:30:45, 2012^12^31 11+30+45, 2012/12/31 11*30*45, 2012@12@31 11^30^45 都是相同的
日期和时间使用 T 分隔和使用 分隔是相同的。2012-12-31 11:30:45 和 2012-12-31T11:30:45 是相同的
没有分隔符的字符串 YYYYMMDDHHMMSS YYMMDDHHMMSS 将被视为 DATETIME。20070523091528 和 070523091528 被解释为 2007-05-23 09:15:28, 但 071122129015 是不合法的，将被解释为 0000-00-00 00:00:00
数字 YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 可以作为 DATETIME. 19830905132800 和 830905132800 被解释为 1983-09-05 13:28:00

NULL与空

新建 NOT NULL 表

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS t_test_not_null;

CREATE TABLE t_test (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    age INT NOT NULL,
    create_time DATETIME NOT NULL,
    create_date DATE NOT NULL,
    create_timestamp TIMESTAMP NOT NULL
);

插入默认数据
INSERT INTO t_test_not_null VALUE();

查看生成的默认值
SELECT * FROM t_test_not_null;

1
2
3
    id  name       age  create_time          create_date     create_timestamp  
------  ------  ------  -------------------  -----------  ---------------------
     1               0  0000-00-00 00:00:00  0000-00-00     0000-00-00 00:00:00

新建 NULL 表

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS t_test_null;

CREATE TABLE t_test_null (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    age INT,
    create_time DATETIME,
    create_date DATE,
    create_timestamp TIMESTAMP
);

插入默认数据
INSERT INTO t_test_null VALUE();

查看生成的默认值
SELECT * FROM t_test_null;

1
2
3
    id  name       age  create_time  create_date  create_timestamp  
------  ------  ------  -----------  -----------  ------------------
     1  (NULL)  (NULL)  (NULL)       (NULL)                   (NULL)

结论：

  1. 在字段设置 NOT NULL 的情况下，如果不指定该字段的值，MySQL 会自动插入默认值
    DATE DATETIME TIMESTAMP 的默认值为 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00
    TINYINT INT BIGINT 的默认值为 0
    VARCAHR 的默认值为 '' 空字符串
  2. 字段没有设置 NOT NULL，不设置值时会自动插入 NULL
  3. 查询为 NULL 的行时，需要使用 IS NULL 和 IS NOT NULL 来设置查询条件，使用 IS NULL 来查询 VARCHAR 类型时不能查出为空字符串的值，查询空字符串需要使用 col = ''

索引

独立的列

独立的列指索引的列不能是表达式的一部分，也不能是函数的参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DROP TABLE IF EXISTS t_index_one;
CREATE TABLE t_index_one(
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10),
	age INT,
	KEY(`age`)
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO t_index_one(`name`, age) VALUE('test1', 11);
INSERT INTO t_index_one(`name`, age) VALUE('test2', 12);
INSERT INTO t_index_one(`name`, age) VALUE('test3', 13);

EXPLAIN SELECT * FROM t_index_one WHERE age - 1 = 11;
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       t_index_one  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       3    100.00  Using where  

EXPLAIN SELECT * FROM t_index_one WHERE age = 11;
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra   
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  --------
     1  SIMPLE       t_index_one  (NULL)      ref     age            age     5        const        1    100.00  (NULL)

多列索引和左前缀列
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
DROP TABLE IF EXISTS t_index_two;
CREATE TABLE t_index_two(
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10),
	age INT,
	`password` VARCHAR(20),
	KEY(`name`, `age`)
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO t_index_two(`name`, age, `password`) VALUE('test1', 11, '111111');
INSERT INTO t_index_two(`name`, age, `password`) VALUE('test2', 12, '111111');
INSERT INTO t_index_two(`name`, age, `password`) VALUE('test3', 13, '1111111');

EXPLAIN SELECT * FROM t_index_two WHERE `name` = 'test1';
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra   
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  --------
     1  SIMPLE       t_index_two  (NULL)      ref     name           name    43       const        1    100.00  (NULL)  


EXPLAIN SELECT * FROM t_index_two WHERE `name` = 'test1' AND age > 11 AND age < 13;
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra                  
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  -----------------------
     1  SIMPLE       t_index_two  (NULL)      range   name           name    48       (NULL)       1    100.00  Using index condition  



EXPLAIN SELECT * FROM t_index_two WHERE `name` = 'test%';
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra   
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  --------
     1  SIMPLE       t_index_one  (NULL)      ref     name           name    43       const        1    100.00  (NULL)  

EXPLAIN SELECT * FROM t_index_two WHERE  age = 11;
    id  select_type  table        partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  -----------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       t_index_two  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       3     33.33  Using where
覆盖索引

如果一个索引包含所有需要查询的字段的值，那么我们就之为覆盖索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DROP TABLE IF EXISTS t_index_three;
CREATE TABLE t_index_three(
	id BIGINT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(10),
	age INT,
	`password` VARCHAR(20),
	KEY(`name`, `age`)
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO t_index_three(`name`, age, `password`) VALUE('test1', 11, '111111');
INSERT INTO t_index_three(`name`, age, `password`) VALUE('test2', 12, '111111');
INSERT INTO t_index_three(`name`, age, `password`) VALUE('test3', 13, '1111111');

EXPLAIN SELECT * FROM t_index_three;
    id  select_type  table          partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra   
------  -----------  -------------  ----------  ------  -------------  ------  -------  ------  ------  --------  --------
     1  SIMPLE       t_index_three  (NULL)      ALL     (NULL)         (NULL)  (NULL)   (NULL)       3    100.00  (NULL) 

EXPLAIN SELECT `name`, `age` FROM t_index_three;
    id  select_type  table          partitions  type    possible_keys  key     key_len  ref       rows  filtered  Extra        
------  -----------  -------------  ----------  ------  -------------  ------  -------  ------  ------  --------  -------------
     1  SIMPLE       t_index_three  (NULL)      index   (NULL)         name    48       (NULL)       3    100.00  Using index

索引和锁

新建表

1
2
3
4
5
6
7
8
9
10
DROP TABLE IF EXISTS example_one;
CREATE TABLE example_one (
	id BIGINT PRIMARY KEY,
	`name` VARCHAR(20),
	`age` INT
) ENGINE = INNODB DEFAULT CHARSET utf8mb4;

INSERT INTO example_one(id, `name`, age) VALUES(1, 'test1', 10);
INSERT INTO example_one(id, `name`, age) VALUES(2, 'test2', 10);
INSERT INTO example_one(id, `name`, age) VALUES(3, 'test3', 10);

使用主键锁定一行

1
2
BEGIN;
SELECT * FROM example_one WHERE id = 1 FOR UPDATE;

打开一个新的 session 使用主键查询锁定这一行会被阻塞，行锁

1
2
BEGIN;
SELECT * FROM example_one WHERE id = 1 FOR UPDATE;

使用主键查询锁定另一行不会被阻塞

1
2
BEGIN;
SELECT * FROM example_one WHERE id = 2 FOR UPDATE;

使用别的条件锁定会被阻塞

1
2
BEGIN;
SELECT * FROM example_one WHERE `name` = 'test1' FOR UPDATE;

为 name 列建立索引

1
ALTER TABLE example_one ADD INDEX name_idx(`name`);

使用 name 锁定别的行不会被阻塞

1
2
BEGIN;
SELECT * FROM example_one WHERE `name` = 'test2' FOR UPDATE;

锁定 id = 1 的行会被阻塞

1
2
BEGIN;
SELECT * FROM example_one WHERE `name` = 'test1' FOR UPDATE;