MySql超好用的SQL语法

我们使用SQL时多是执行增删改查，但遇到复杂问题，感觉像是不够用，这时MySql特有的语法就会发挥效用。下面进行汇总。

1.防止重复插入

我们常用的防止重复插入的方法是先检查是否已经存在，不存在再插入。如下：

/*若重复插入一个有唯一性索引的数据，系统会抛出异常*/
insert into user_info (username,mobile,update_time) values('test','13836968688', now());

/*我们常用的方式是先检查，不存在再插入*/
insert into user_info (username,mobile,update_time) 
select 'test','13836968688', now() from user_info where not exists (select 1 from user_info where username = 'test');

/*第二种办法是使用 ignore关键字，以忽略插入异常，并返回修改行数。*/
insert ignore into user_info (username,mobile,update_time) values('test','13836968688', now());

/*若系统中无相关数据，则新增；若有则更新指定字段；用来处理高并发情况下的数据插入。*/
/*注意:在高并发的场景下使用on duplicate key update语法，可能会存在死锁的问题，所以要根据实际情况酌情使用。*/
INSERT  into user_info (username,mobile,update_time) values('test','13836968688', now())
 on duplicate key update mobile='13836968688',edit_date=now();

2.select ... for update

Mysql自身都有悲观锁，InnoDB在增删改查时使用的是基于数据行的行锁，且使用了MVCC(多版本并发控制)。为了防止在基于原有数据进行更新时有重复操作，需要在查询时加上悲观锁，在本事务提交之前防止对此数据的其他修改操作。但这种操作一般适用于并发量不大的数据操作。

begin;
select * from user_score where username = 'test' for update;
/*统计需要新增的积分*/
update user_score set score = score + 10 where username = 'test';
commit;

3.查看数据表结构

我们在客户端有时候想快速了解数据表的结构，一般会通过客户端提供的数据表查看功能，但有更方便的方式，就是通过Sql命令的方式查看。

/*查看数据表中的字段信息*/
desc user_info;

/*查看数据表的索引信息*/
show index from user_info;

/*通过生成创建数据表的Sql查看数据表结构（包括字段和索引等全部信息）*/
show create table user_info;

/*查询分析，重点分析每一步的扫描行数和索引使用情况*/
explain select * from user_info where username = 'test';

4.备份数据表

一般情况下我们备份整个数据库，但有时仅需备份一个数据表，这时我们通常先新建一个形同结构的数据表，然后再复制数据。但还有一步到位的方法，如下：

/*新建相同结构的数据表，新建的数据表与原来的一样，包括字段和索引*/
create table tmp_user_info like user_info;

/*复制数据*/
insert into tmp_user_info select * from user_info;

/*一步到位的备份方法，但是仅创建了相同的字段，却没有创建索引。*/
create table tmp_user_info select * from user_info;

5.查看当前线程show processlist

当遇到数据库锁死时，或者执行很慢时，或者并发比较高时，都可以通过查看当前线程来协助分析。

/*显示当前数据库的运行线程，由于线程池的存在，当前线程包括正在执行的线程和空闲的线程。
 线程信息包括执行线程的用户，客户端Host,执行时长，状态和命令信息。*/
show processlist;

/*查询锁死的线程ID，通过下面语句杀死线程，10为线程ID*/
KILL QUERY 10;

6.数据库备份与还原

在数据库迁移时，搭建测试环境时，为了数据安全考虑定时备份数据库时都可以通过命令备份数据库。

/*备份数据库*/
mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql;

/*还原数据库*/
use dbname;
source backup.sql;

若数据库比较大，则需要压缩备份后的文件。可以先如上备份，然后再通过压缩程序压缩。还原时，先解压再还原。但这样不免有些麻烦，我们完全可以利用管道一次性备份和压缩及一次性解压和还原，代码如下：

/*备份并使用gzip压缩脚本文件,其中>代表输出。*/
mysqldump -h192.168.1.20 -uadmin -padmin123456 good_life | gzip > good_life.sql.gz

/*使用gzip解压脚本文件并还原，其中<代表输入。*/
gzip -d < good_life.sql.gz | mysql -h192.168.1.100 -uadmin -padmin123456 good_life

/*只要压缩软件支持管道都可以用来压缩备份文件，例如7z.其中a表示加入压缩文件，-si表示输入，-mmt6表示开启6个线程.*/
mysqldump -h192.168.1.20 -uadmin -padmin123456 good_life | 7z a -si good_life.sql.7z  -mmt6 -mx3 -aoa

/*使用7z解压并还原数据库,其中x表示解压，-so表示输出*/
7z x -so good_life.sql.7z | mysql -h192.168.1.100 -uadmin -padmin123456 good_life

7.BINARY 操作符

在MySQL中，BINARY操作符将一个字符串转为二进制字符串。二进制字符串使用binary字符集和binary排序规则。BINARY操作符常用在对字符串进行逐字节比较而不是逐字符比较。

 MySQL BINARY 操作符的语法：BINARY expr 等同于 CONVERT(expr, BINARY) 和 CAST(expr AS BINARY) 函数。

一般字符串字段不识别大小写，通过BINARY操作符比较，可以区分大小写。

8.添加索引

若逾期数据表比较的大，最好在设计数据表时就创建索引，否则当数据量增加到百万甚至千万以上后，再为数据表添加索引就会特别慢。但还是有些办法的，例如再添加索引时关闭慢查询日志，添加完成后再打开。

/*关闭慢查询日志*/
SET GLOBAL slow_query_log = 0;
/*尽量使用 create index语法，比使用 alter table 语法更快。*/
CREATE index idx_customer_name ON  customer(name);
/*开启ON*/
SET GLOBAL slow_query_log = 1;

其实若需要为一个大表的同一个字段添加一般索引和全文索引，可以新建一个小表，此表中只包含原表主键和需要建立全文索引的字段，需要用到全文索引时才关联此表。

9.locate

函数locate(key,col)查找关键字在在字符串（列）中的位置。

select name,locate('供应链',name) as loc 
from customer 
where id = 1;

select locate('供应链','东莞市莞鹏辉供应链管理有限公司') as loc;

10. insert ignore 

有时需要同步数据或调整数据时需要用到 insert ... select ...语句，但担心数据重复插入报异常，则需要在执行时先判断数据库中是否存在，当不存在时再插入。但有更好的办法，就是在insert 后添加 ignore，这时就会忽略异常，且能把不重复的正常插入。

INSERT ignore INTO customer(`id`, `code`, `name`) 
VALUES (123, '108', '张三', now(3));

11.on duplicate key update

通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数据。如果已存在，则不插入数据，而直接返回结果。若系统的并发性很低的情况下，这种方法是合适的。但如果插入数据的请求，有一定的并发量，这种做法就可能会产生重复的数据或者抛出异常。当然防止重复数据的做法很多，比如：加唯一索引、加分布式锁等。但这些方案，都没法做到让第二次请求也更新数据，它们一般会判断已经存在就直接返回了。这种情况可以使用on duplicate key update语法。
该语法会在插入数据之前判断，如果主键或唯一索引不存在，则插入数据。如果主键或唯一索引存在，则执行更新操作。具体需要更新的字段可以指定，例如：

INSERT  INTO customer(`id`, `code`, `name`, `update_time`) 
VALUES (123, '108', '张三', now(3))
on duplicate key update name='张三',update_time=now(3);

12.create table ... select

有时我们需要创建一个数据表的备份，一般流程为新建备份表，然后把查询原数据表的数据并插入新数据表中。但我们也可以通过一条SQL语句创建备份表并插入数据。

/*1.创建数据表*/
create table customer_20231229 like customer;
/*2.插入数据*/
insert into customer_20231229 select * from customer;

/*直接创建数据表并插入数据*/
create table customer_20231229  select * from customer;

13.explain select...

我们可以通过索引来提高数据查询性能，但有时索引的效果没有发挥出来，我们可以通过explain select...来查询索引的使用情况。

explain select * from customer where name like '张山%'

若查询条件或排序字段中使用了建立了索引的列，但索引却没有发挥效果，一般原因如下：

1. 不满足最优前缀原则；
2. 范围索引列没有放在最后；
3. 使用了 select *；
4. 索引列上有计算；
5. 索引列上使用了函数；
6. 使用了 关键字 in ；
7. 用is null 或 is not null，但列本身不允许为空；
8. like 的文本左边有%；
9. 查询条件中使用了or关键字；
10. 使用了类型转换；

14. Select语句的执行顺序

FROM
<left table>
ON
<join_condition>
<join_type>
JOIN
<right_table>
WHERE
<where condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
SELECT
DISTINCT
<select list>
ORDER BY
<order_by_condition>
LIMIT
<limit number>