MySQL技巧

优质文章收集

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_admin
for each row
begin
declare v_icode int;
declare v_duplicate int;
	repeat
		set 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=0
	end 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()) age
from 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.columns
where table_name='the_name';

批量插入

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

--方法1
insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa');
insert into TABLE_NAME (ID,OTHER_COLUMN) values (1,'aaa'); 
--方法2
insert 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) ) b
limit 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' 的结果是相同的。

#内表较大情况使用exists
select userid from analytics.d_user_basic dub
where exists(select 1 from analytics.f_business_cml fbc where dub.userid=fbc.userid);
#内表较小情况使用in
select userid from analytics.d_user_basic dub
where 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 tt
      join analytics.d_user_basic dub on tt.userid=dub.userid
      join analytics.d_user_product dup on tt.userid=dup.userid
      where tt.epocket_last_time<=date_sub(curdate(),interval 30 day)
      and push_history is null
      order 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 tt
      join (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.id
      join analytics.d_user_basic dub on tt.userid=dub.userid
      join analytics.d_user_product dup on tt.userid=dup.userid


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值