需求

期末考试成绩出来了，结果需要分为不及格(0-59)，合格(60-84),优秀(80+)三个段，老师需要统计每个段成绩的分布情况。

分数表score结构如下：

id int
user_id int
score int

使用INTERVAL函数对学生成绩分段

SELECT user_id,score,INTERVAL(score,60,85) as step from score

INTERVAL函数会根据score返回0（少于60）,1（60-74）,2（85以及以上）

将INTERVAL返回的结果进行分组:

SELECT INTERVAL(score,60,85),COUNT(*) AS num as step from score GROUP BY INTERVAL(score,60,85)

得到：

0, 5
1, 80
2, 15

使用ELT函数为阶段命名

ETL方法会建立重命名，将1，2，3分别转化为不及格，合格，优秀，但是etl不能是从0开始的，所以我们可以在INTERVAL分组时加一个0，将0分以下的划分为0,0-59的划为1，依次类推

SELECT INTERVAL(score,0,60,85),COUNT(*) AS num as step from score GROUP BY INTERVAL(score,60,85)

得到：

1, 5
2, 80
3, 15

最终SQL:

SELECT ELT(INTERVAL(score,0,60,85),"不及格","合格","优秀"),COUNT(*) AS num as step from score GROUP BY ELT(INTERVAL(score,0,60,85),"不及格","合格","优秀")

得到：

不及格, 5
合格, 80
优秀, 15

今天在v2ex看到有人问怎么建表，其实建表个人总结起来非常简单。

用面向对象方法建立表

class = table
object = row
property = field

如：（我习惯用单数，你可以用复数）

book:
id
title,
isbn

category:
id
name

book_category:
id
book_id
author_id

处理表关联关系

• A1 对多 B：B 表加 a_id，比如 order_item 表加 order_id
• A1 对 1B：附表加主表 id，比如 profile 表加 user_id
• A 多对多 B：C 表关联 A，B，如 post_tag 表加 post_id 和 tag_id

如何建立索引（全文索引）

• 通常常用的where条件增加索引，如select id,title from post where forum_id=3，这时候需要给forum_id加索引，ID是主键是不要加的，title也不要加
• 数据库模糊查询（如搜索“Mysql 数据库”）需要建立全文索引（参考我的另外一篇文章：Mysql中文全文索引（含实例5分钟上手））
• 排序可以加组合索引，如select id,title from post where forum_id=3 order by create_time desc，这时候可以加forum_id+create_time的组合索引，Mysql高版本组合索引可以设置排序
• 组合索引单独查后部分不起作用，如上面的forum_id+create_time的组合索引，如果你只根据create_time查那么不起作用
• 唯一字段可以加唯一索引

Mysql性能工具

• explain可以分析sql性能，每个字段的含义都需要清楚
• slow query log:Mysql的配置中开启慢速查询日志，慢速查询日志需要用慢查询分析工具来分析，不是用肉眼来看的
• show full processlist可以查看当前数据库运行的查询
• infomation_schema有一大堆有用东西

其他

• null还是0：建议采用null表示没有，这样不会影响数据库的约束

有用的资源

• 求助帖: 上线之后数据库 CPU 升高,找不到原因

查看那张表占用空间大

USE information_schema;

SELECT
TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH
FROM
`TABLES`
ORDER BY
DATA_LENGTH DESC;

修改表采用压缩

USE mydb;

ALTER TABLE mytable ROW_FORMAT = DYNAMIC;

OPTIMIZE TABLE mytable;

如果没有 DYNAMIC，那么采用 COMPRESSED 也可以

修改表采用 tokudb （需要安装 tokudb 引擎）

ALTER TABLE `mytable`
ENGINE=tokudb;

修改字符集

ALTER TABLE `mytable`
DEFAULT CHARACTER SET=gbk

删除日志文件

第一步：登陆进入mysql，并使用 show binary logs; 查看日志文件。

mysql> show binary logs;

第二步：查看正在使用的日志文件：show master status;

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 425994852 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

当前正在使用的日志文件是mysql-bin.000005，那么删除日志文件的时候应该排除掉该文件。

mysql> purge binary logs to 'mysql-bin.000005';

删除除mysql-bin.000005以外的日志文件。

mysql 定时清理日志文件

如果每次等到发现空间不足的时候才去手动删除日志文件，这种方式是很不理想的。

那么，我们就需要设置mysql，让它能自动清理日志文件。

编辑mysql的配置文件，设置expire_logs_days（mysql定时删除日志文件）

[root@sam ~]# vim /etc/my.cnf

在my.cnf中，添加或修改expire_logs_days的值 (这里设置的自动删除时间为10天, 默认为0不自动删除)

expire_logs_days=10

修改后，重启mysql就会生效。

但是，在生产环境中，重启mysql数据库往往会付出很高的代价。

于是，可以在不重启mysql的情况下，修改expire_logs_days值

如下：

mysql> show variables like '%log%';
mysql> set global expire_logs_days = 10;

设置完后，可以通过 show variables like ‘%log%’; 看到expire_logs_days的值已被修改成10。

注意：通过这种方式设置expire_logs_days虽然不需要重启mysql即可生效，但是该方式在重启mysql之后，值会被恢复。

于是，建议通过mysql命令设置expire_logs_days的同时，也修改/etc/my.cnf下的expire_logs_days=10配置，这样在下次重启mysql的时候，expire_logs_days也一样是10；

参考

• mysql 日志爆满，删除日志文件，定时清理日志

根据下级状态更新上级状态

原来语句

SET display = 1
WHERE
    id IN (
        SELECT
            a.pid
        FROM
            area a
        WHERE
            a.display = 1
        AND pid != 0
    );

提示：[Err] 1093 – You can’t specify target table ‘area’ for update in FROM clause

修改后

UPDATE area a1,(
        SELECT
            pid
        FROM
            area
        WHERE
            display = 1
        AND pid != 0
    ) a2
SET a1.display = 1
where a1.id=a2.pid;

Mysql 8.0.12 新增 instant 算法，秒级添加字段

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=instant

看了网上很多回答，基本都是过时和错误的。

5.6 以后增加了ONLINE DDL，

实现不锁表增加字段和索引非常简单。

解决办法

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE

ALGORITHM表示算法：default默认（根据具体操作类型自动选择），inplace（不影响DML），copy创建临时表（锁表），INSTANT只修改元数据（8.0新增，在修改名字等极少数情况可用）

LOCK表示是否锁表：default默认，none，shared共享锁，exclusive

什么情况下会inplace，什么情况下会copy？

什么情况下会inplace，什么情况下会copy，具体参考：Online DDL Operations

5.6以前版本解决办法

• GitHub’s Online Schema Migrations for MySQL (gh-ost)
• pt-online-schema-change (pt-osc)

参考

• MySQL Online DDL，还是要谨慎
• InnoDB and Online DDL

https://wenku.baidu.com/view/d2d4f15ba8114431b80dd81f.html

vim /etc/mysql/my.cnf

 [client] 

 default-character-set = utf8 

 [mysqld] 

 character-set-server=utf8 

 collation-server=utf8_general_ci 

 skip-character-set-client-handshake 

 [mysql] 

 default-character-set = utf8