MySQL大杂烩

1. 触发器

举个栗子:数据表person中有个字段叫updated,记录更新次数。初始为0,每次更新数据都加1

1
2
3
4
5
6
7
8
DROP TRIGGER IF EXISTS my_trigger;
CREATE TRIGGER my_trigger
BEFORE UPDATE
ON person
FOR EACH ROW
BEGIN
SET NEW.updated = OLD.updated + 1;
END;

OLD保存旧数据,NEW保存新数据

注意:更新NEW只能使用BEFORE触发器

2. 允许多查询

通过MySQl驱动执行SQL时,有时需要一次执行多条SQL。默认情况是不支持的,需要手动开启。以JDBC为例:

jdbc:mysql://localhost:3306/foo?allowMultiQueries=true

这里的多查询,不是特指SELECT。任意语句都可。

3. 日期格式化

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')

结果

2018-06-28 11:40:10

4. 存储过程示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE foo;
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
school_id CHAR(4) NOT NULL,
student_id CHAR(6) NOT NULL,
INDEX school_id(school_id),
INDEX student_id(student_id),
INDEX school_id_and_student_id(school_id, student_id)
);
DROP PROCEDURE IF EXISTS tmpproc;
CREATE PROCEDURE tmpproc() BEGIN
DECLARE i INT UNSIGNED DEFAULT 0;
WHILE i < 10000000 DO
INSERT INTO tmp (school_id, student_id)
VALUES (SUBSTR(MD5(RAND()) FROM 1 FOR 4), SUBSTR(MD5(RAND()) FROM 1 FOR 6));
SET i = i + 1;
END WHILE;
END;
CALL tmpproc();

5. 透视表结构

1
2
3
4
5
6
7
8
9
10
11
SELECT COLUMN_NAME                 AS 字段,
UPPER(COLUMN_TYPE) AS 类型,
IFNULL(COLUMN_DEFAULT, '无') AS 默认值,
CASE COLUMN_KEY
WHEN 'PRI' THEN '主键索引'
WHEN 'UNI' THEN '唯一索引'
WHEN 'MUL' THEN '普通索引'
ELSE '无' END AS 索引,
COLUMN_COMMENT AS 注释
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'wb_sensor_message';

6. 查询缓存

  • 查看查询缓存是否开启: SHOW VARIABLES LIKE 'query_cache_type'
  • 不带缓存进行查询: SELECT SQL_NO_CACHE * FROM tmp

MySQL的查询缓存很鸡肋,一般没什么命中率。默认是关闭状态,MySQL8中直接废除了查询缓存。

7. 查询透视

EXPLAIN SELECT * FROM tmp;DESCRIBE SELECT * FROM tmp;

8. SELECT then INSERT

1
2
CREATE TABLE lorem ( id INT ); 
INSERT INTO lorem SELECT 1;

9. 查询日志

  • 获取查询日志状态 SHOW variables LIKE 'general_log'
  • 开启查询日志 SET GLOBAL general_log = 'ON';

10. 查询InnoDB表的索引大小

1
2
3
4
5
SELECT index_name, ROUND(SUM(stat_value) * @@innodb_page_size / 1024 / 1024, 2)SizeMB
FROM mysql.innodb_index_stats
WHERE table_name = 'tmp'
AND stat_name = 'size'
GROUP BY index_name;

示例结果:






index_nameSizeMB
PRIMARY312.80
school_id196.77
school_id_and_student_id294.00
student_id264.00

11. InnoDB表按表大小排序

1
2
3
SELECT *
FROM mysql.innodb_table_stats
ORDER BY n_rows DESC;

12. MySQL窗口函数

MySQL在版本8之后新增了窗口函数,方便处理分组排序的问题

查询结果添加行号

1
SELECT ROW_NUMBER() OVER(), user_id, action FROM log;





ROW_NUMBER() OVER()user_idaction
11LOGIN
21LOGOUT
32LOGOUT
42LOGIN

分组排序示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE log (
user_id INT,
action VARCHAR(32)
);

INSERT INTO log (user_id, action)
VALUES (1, 'LOGIN'),
(1, 'LOGOUT'),
(2, 'LOGOUT'),
(2, 'LOGIN');

SELECT user_id, action
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY action), user_id, action FROM log)$
GROUP BY user_id;



user_idaction
1LOGIN
2LOGIN

MySQL获取随机行

方式一 ORDER BY RAND()

示例:

1
SELECT * FROM foo ORDER BY RAND() LIMIT 1;

优点: 简单, 缺点: 慢

方式二 JOIN随机数

示例:

1
2
3
4
SELECT foo.*
FROM foo
JOIN (SELECT RAND() * MAX(id) AS id FROM foo) AS random ON foo > random.id
LIMIT 1;

优点: 快, 缺点: 不够直观,不够随机,一次只能获取一行

MySQL备份

例: mysqldump -h localhost -u foo -p'foo' foo -B user | pv > user.sql

  • -h 指定MySQL服务器
  • -u 指定用户名
  • -p 指定密码,中间没空格,可加引号
  • -B 指定数据库名
  • pv 显示进度条
  • > user.sql 保存到文件

Java执行MySQL脚本

iBatis提供了SqlRunner类,可以用来执行SQL脚本。

为了不引入外部依赖,可以使用单独的SqlRunner

Maven配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<dependencies>
<dependency>
<groupId>com.github.baijifeilong</groupId>
<artifactId>ScriptRunner</artifactId>
<version>master</version>
</dependency>
</dependencies>


<repositories>
<repository>
<id>jitpack</id>
<url>https://jitpack.io</url>
</repository>
</repositories>

Java示例代码

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
package bj.sqlrunner;

import com.zaxxer.hikari.HikariDataSource;
import lombok.SneakyThrows;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.io.FileReader;
import java.nio.file.Files;
import java.nio.file.Paths;

/**
* Created by BaiJiFeiLong@gmail.com at 2018/12/25 下午4:03
*/
public class App {

private static final String SQL = "DROP TABLE IF EXISTS user;\n" +
"CREATE TABLE user (\n" +
" id INT PRIMARY KEY AUTO_INCREMENT,\n" +
" mobile CHAR(11) NOT NULL,\n" +
" encrypted_password VARCHAR(64) NOT NULL\n" +
");";

@SneakyThrows
public static void main(String[] args) {
String resourceRoot = ClassLoader.getSystemResource(".").getFile();
String sqlFilename = resourceRoot + "/user.sql";
Files.write(Paths.get(sqlFilename), SQL.getBytes());

DataSource dataSource = new HikariDataSource() {{
setJdbcUrl("jdbc:mysql://localhost/foo");
setUsername("root");
setPassword("root");
}};

new ScriptRunner(dataSource.getConnection()).runScript(new FileReader(sqlFilename));

System.out.println("Created table has columns:");
new JdbcTemplate(dataSource).queryForList(
"SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS " +
"WHERE TABLE_SCHEMA = 'foo' AND TABLE_NAME = 'user'")
.forEach(System.out::println);
}
}

控制台输出

1
2
3
4
Created table has columns:
{COLUMN_NAME=id, COLUMN_TYPE=int(11)}
{COLUMN_NAME=mobile, COLUMN_TYPE=char(11)}
{COLUMN_NAME=encrypted_password, COLUMN_TYPE=varchar(64)}

注意

  • 为了方便演示,没有准备文件,直接创建了一个新文件

MySQL的一些特殊设置

  • set @@SQL_SELECT_LIMIT=10 设置全局Limit(仅限查询结果,不限子查询)

MySQL的一些特殊用法

  • 当计算器使用 SELECT 8 * 8
  • 查看当前日期时间 SELECT CURRENT_TIMESTAMP
  • 计算三角函数 SELECT SIN(30 / 180 * PI())
  • 计算MD5 SELECT MD5('password')
  • 生成UUID SELECT UUID()
  • 查ASCII码 SELECT ASCII('A')
  • 查二进制 SELECT BIN(1024)
  • 查十六进制 SELECT HEX(1024)
  • 获取随机数 SELECT RAND()
  • 日期计算 SELECT ADDDATE(NOW(), INTERVAL -18 YEAR)
  • 分支 SELECT CASE 12345 WHEN 1 THEN 'ONE' ELSE 'NOT ONE' END
  • 赌大小 SELECT IF(random.number > 0.5, 'BIG', 'SMALL') FROM (SELECT RAND() AS number) AS randomSELECT IF(RAND() > 0.5, 'BIG', 'SMALL');

使用MySQL注意事项

  • MySQL数据库默认不存Unicode,得显式设置表或列的编码(CHARSET utf8)
  • MySQL连接默认不走Unicode,得显式设置(characterEncoding=utf8)。useUnicode=true不用设置
  • useSSL=false可以禁用SSL警告

文章首发: https://baijifeilong.github.io/2018/10/04/mysql