4 MySQL 语法技巧

MySQL 语法技巧

1 where 语句中实现 case when

• case

WHERE
  pw='correct'
  AND CASE WHEN id<800 THEN success=1 ELSE TRUE END 
  AND YEAR(timestamp)=2011

goals：return success=1 (which can be TRUE or FALSE) in case id < 800, or always return TRUE otherwise.

• solution

where
    pw = 'correct'
    and (id > 800 or success = 1)
    and year(timestamp)=2011

2 order by before group by

• case

SELECT wp_posts.* FROM wp_posts
    WHERE wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
    GROUP BY wp_posts.post_author           
    ORDER BY wp_posts.post_date DESC

goals：The example query produces unusable results as its not always the latest post that is returned.

• solution

subquery：

SELECT wp_posts.* FROM 
(
    SELECT * 
    FROM wp_posts
    ORDER BY wp_posts.post_date DESC
) AS wp_posts
WHERE wp_posts.post_status='publish'
AND wp_posts.post_type='post'
GROUP BY wp_posts.post_author

join（the best solution!）：

select * from `wp_posts`
inner join 
(
    select max(post_date) as `max_post_date`, post_author
    from `wp_posts`
    where wp_posts.post_status='publish'
        AND wp_posts.post_type='post'
    group by wp_post.post_author
) as p
    on wp_posts.post_author = p.post_author
    and wp_posts.post_date = p.max_post_date
where wp_posts.post_status='publish'
    AND wp_posts.post_type='post'
order by wp_posts.post_date desc

3 COALESCE() 函数

此函数返回参数中第一个不为 null 参数的值

• case

goals：取出系统分、专家评分，若专家分不为 null 则以专家评分为准

• solution

coalesce(sme_score, sys_score, 0)

4 load 命令

load 在大批量的导入和导出数据时效率非常高

LOAD DATA LOCAL INFILE 'C:/Users/hkf/Desktop/task_ningxia.txt' REPLACE INTO TABLE `task_ningxia` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

select into file 导出数据

SELECT * FROM m_drug INTO OUTFILE '/mnt/share/m_drug.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

5 utf8_bin 区分大小写

• utf8_bin：将字符串中的每一个字符用二进制数据存储，区分大小写。
• utf8_genera_ci：不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

6 MySQL Migration Wizard

迁移包含大量数据的表或库，其本质上还是使用 load 命令实现的。
此处可以使用 MySQL Workbench 的迁移向导完成，其内容详见 MySQL Workbench Migration Wizard

Note：大的插入和更新会受 max_allowed_packet 参数的限制，当有 text 和 Blob 字段时，应先修改参数值再进行后续操作

7 MySQL 字符串函数

详见链接 MySQL字符串函数

8 if and case when 语句

if(confition, expr1, expr2)：select if(id>100, 'new', 'older') as test

case when 语句有2种形式的用法：

case gender
when 1 then '男'
when 0 then '女'
else 'unknown'
as `gender_text`

case 
when gender = 1 then '男'
when gender = 0 then '女'
else 'unknown'
as `gender_text`

9 show processlist

MySQL 的 show processlist 命令输出有哪些线程在运行，可以借助此命令帮助我们定位有问题的 sql 语句。

其输出结果中的 Time 列，用于标识这个状态持续的时间。

root 用户使用 show full processlist，可以查看所有用户运行的线程。

10 嵌套事务

MySQL 不允许事务嵌套，当 sql 遇到 start transaction 时就会隐式的提交。

mysql> select * from ceshi;  
+------+  
|  n   |  
+------+  
|    1 |  
+------+  
1 row in set (0.00 sec)  

mysql> start transaction;  
Query OK, 0 rows affected (0.00 sec)  

mysql> insert into ceshi values(2);  
Query OK, 1 row affected (0.00 sec)  

mysql> start transaction ;  
Query OK, 0 rows affected (0.00 sec)  

mysql> insert into ceshi values(3);  
Query OK, 1 row affected (0.00 sec)  

mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  

mysql> rollback;  
Query OK, 0 rows affected (0.00 sec)  

mysql> select * from ceshi;  
+------+  
|  n   |  
+------+  
|    1 |  
+------+ 
|    2 |  
+------+ 
|    3 |  
+------+
1 row in set (0.00 sec)  

11 explain

explain 命令可分析 SQL 语句，定位问题所在。详见 MySQL explain 详解

另外，对于联表查询，尽量的优化大表的查询，保证优化后数据量较大的表没有出现 using temporary 或 using filesort