MySQL大杂烩

1. 触发器

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

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. 存储过程示例

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. 透视表结构

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

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

9. 查询日志

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

10. 查询InnoDB表的索引大小

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;

示例结果:

<table border="1" style="border-collapse:collapse"> <tr><th>index_name</th><th>SizeMB</th></tr> <tr><td>PRIMARY</td><td>312.80</td></tr> <tr><td>school_id</td><td>196.77</td></tr> <tr><td>school_id_and_student_id</td><td>294.00</td></tr> <tr><td>student_id</td><td>264.00</td></tr></table>

11. InnoDB表按表大小排序

SELECT *
FROM mysql.innodb_table_stats
ORDER BY n_rows DESC;

12. MySQL窗口函数

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

查询结果添加行号

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

<table border="1" style="border-collapse:collapse"> <tr><th>ROW_NUMBER() OVER()</th><th>user_id</th><th>action</th></tr> <tr><td>1</td><td>1</td><td>LOGIN</td></tr> <tr><td>2</td><td>1</td><td>LOGOUT</td></tr> <tr><td>3</td><td>2</td><td>LOGOUT</td></tr> <tr><td>4</td><td>2</td><td>LOGIN</td></tr></table>

分组排序示例

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;

<table border="1" style="border-collapse:collapse"> <tr><th>user_id</th><th>action</th></tr> <tr><td>1</td><td>LOGIN</td></tr> <tr><td>2</td><td>LOGIN</td></tr></table>

MySQL获取随机行

方式一 `ORDER BY RAND()`

示例:

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

优点: 简单, 缺点: 慢

方式二 `JOIN`随机数

示例:

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配置

<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示例代码

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);
    }
}

控制台输出

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 random 或 SELECT 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