MySQL技巧

优质文章收集

• MySQL索引原理及慢查询优化
  B+树、最左匹配、区分度的计算 

DDL

DDL=data definition language，主要是对数据库表结构的操作语言，包括create、alter、drop

创建表

一般要带有自增序列的ID、记录数据插入时间的CTIME、记录数据修改时间的UTIME：

--参考语句create table TABLE_NAME (ID int not null auto_increment,OTHER_COLUMN varchar(80),CTIME datetime not null default now(),UTIME datetime not null default now() on update now(),primary key (id));--指定编码create table analytics.tmp(id int) default charset=utf8;--修改编码alter table analytics.tmp convert to character set utf8;

NewCaseFolder.MedicalRecord_Affix表和Event_Attach_R表，在3月28日的改版中，就遗漏了servercreatetime和serverupdatetime。

修改列属性

alter table TABLE_NAME change column COLUMN_NAME NEW_COLUMN_NAME int;-- 举例，增加ctime，utime列alter table NewCaseFolder.Chart_Group add updatetime datetime default now() on update now();alter table NewCaseFolder.Chart_Group add createtime datetime default now();-- 举例，为表中的系统时间增加默认值alter table NewCaseFolder.Chart_Group change column ServerCreateTime ServerCreateTime datetime default now();alter table NewCaseFolder.Chart_Group change column ServerUpdateTime ServerUpdateTime datetime default now() on update now();

结束死锁SQL

有时候，低效的

show processlist;
kill 7314;

创建触发器

对于新插入的数据，分配随机、唯一的邀请码。

DELIMITER //create trigger tr_invite_code before insert on xsl_mada.d_business_adminfor each rowbegindeclare v_icode int;declare v_duplicate int;repeatset v_icode = ROUND(ROUND(RAND(),5)*100000);select count(*) into v_duplicate from xsl_mada.d_business_admin where invite_code=v_icode;until v_icode>10000 and v_duplicate=0end repeat;set NEW.invite_code=v_icode;end//delimiter ;

DML

DML=data manipulation language，主要是对表数据的操作，包括select、insert、update、delete

常见日期计算

更多函数，参见日期和时间函数大全。

常用查询计算列举如下：

--本周周一select DATE_SUB(CURDATE(),INTERVAL WEEKDAY(curdate()) DAY);--上月月初select DATE_SUB(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),INTERVAL DAY(CURDATE())-1 DAY);--本月月初select DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY);--下月月初select date_add(last_day(CURDATE()),interval 1 day);--根据生日计算年龄的两种方法select fld_PatientBirthday,TIMESTAMPDIFF(YEAR,pa.fld_PatientBirthday,curdate()) agefrom db_Hdf.tab_Patient where fld_PatientBirthday!=''limit 100;

字符串操作

常见函数

• concat
• left、right
• substring、substring_index
• instr
• replace

去回车换行

REPLACE(REPLACE(field, CHAR(10),''), CHAR(13),'')

查列名

向一个表insert插入数据的时候，一定要标记列名：

--错误的做法，当TABLE_NAME表新增一列的时候，此SQL将报错insert into TABLE_NAME values('aa','bb');--正确的做法，当TABLE_NAME表新增一列的时候，不会影响此SQL的执行insert into TABLE_NAME (COLUMN1, COLUMN2) values('aa','bb');

当表的列过多时，逐个输入列名会很麻烦，可以使用如下SQL：

select group_concat(column_name) from information_schema.columnswhere table_name='the_name';

批量插入

如果是使用程序插入大量数据，尽量绑定变量。如果是执行SQL批量插入，尽量将多条SQL合并一条。例如：

--方法1insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa');insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'); --方法2insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'),(1,'aaa'); 

上面两种方法，方法2比方法1的效率要搞10+倍。

方法1，每个SQL执行，都需要连接数据库、建立事务、记录日志、执行、关闭事务、关闭数据库连接。对于方法2，只是执行一次流程。

更多的描述参考：MySQL批量SQL插入性能优化

 顺序编号

为记录添加1，2，3这样的顺序编号：

select @rowNum:=IFNULL(@rowNum,0)+1 as rowNo,fmt.upload_record from analytics.f_medicalrecord_total fmt,(Select (@rowNum :=0) ) blimit 100;

通过表名来查所在库

select table_schema from information_schema.tables where table_name ='你要查询的表名（注意要使用大写字母）';

只复制表结构到新表

CREATE TABLE 新表SELECT * FROM 旧表WHERE 1=2

修改已有ID为自增

alter table 表 change ID ID bigint not null auto_increment primary key;

获取表字段 

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'your_table_name' and table_schema = 'your_db_name';

存储过程传递参数中文类型设置

declare v varchar(100) character set utf8;

中文字段拼音排序

select * from TABLE_NAME order by CONVERT(chineseColumnName USING gbk);

更多中文拼音排序可参考：点此进入

Exists和in的性能比较和使用

in 是把外表和内表作hash join，而exists是对外表作loop循环，每次loop循环再对内表进行查询。（关于hash join可参考Merge join vs. Hash join vs. Nested loop ） 

如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 
例如：表A（小表），表B（大表）

1：

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。 

2（与1相反）：

select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。

not in 和not exists

 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 

in 与 =的区别 
select name from student where name in ('zhang','wang','li','zhao'); 与 select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 的结果是相同的。

#内表较大情况使用existsselect userid from analytics.d_user_basic dubwhere exists(select 1 from analytics.f_business_cml fbc where dub.userid=fbc.userid);#内表较小情况使用inselect userid from analytics.d_user_basic dubwhere dub.userid in(select userid from analytics.f_business_cml);

结果中取随机值

1. order by rand()

   • 使用方便，但效率不高，会多次遍历全表排序

      select tt.userid 用户ID, dub.truename 姓名, dub.speciality 科室, dub.standard_hospital 医院, tt.phone 电话,dub.Registerdate 注册时间, tt.epocket_last_time 最后使用时间, dup.epocket_upgrade_time 最后一次升级时间, dup.epocket_cur_version 目前版本,tt.last_action_time 最后一次行为时间, last_action 最后一次行为,tt.action_qty '8-9月行为次数'from analytics.tmp_epocket_action ttjoin analytics.d_user_basic dub on tt.userid=dub.useridjoin analytics.d_user_product dup on tt.userid=dup.useridwhere tt.epocket_last_time<=date_sub(curdate(),interval 30 day)and push_history is nullorder by rand()limit 4500;

     
     

2. round(max(id)*rand())

• • 如果id列不连续将造成随机结果比预期的要小，越不连续越不可靠
  • 多个表相连时，取其中最能决定结果行数的表

  • 对于order by rand()速度慢的情况，可以使用中间表，建立一列自增id列，用来取随机值

    select tt.userid 用户ID, dub.truename 姓名, dub.speciality 科室, dub.standard_hospital 医院, tt.phone 电话,dub.Registerdate 注册时间, tt.epocket_last_time 最后使用时间, dup.epocket_upgrade_time 最后一次升级时间, dup.epocket_cur_version 目前版本,tt.last_action_time 最后一次行为时间, last_action 最后一次行为,tt.action_qty '8-9月行为次数'from analytics.tmp_epocket_action ttjoin (select round(61024*rand()) id from (        select * from analytics.tmp_epocket_action tt where tt.epocket_last_time<=date_sub(curdate(),interval 30 day)        and push_history is null) tt limit 4600)a on tt.id=a.idjoin analytics.d_user_basic dub on tt.userid=dub.useridjoin analytics.d_user_product dup on tt.userid=dup.userid