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