DB
MySQL

MySQL常用SQL总结

查看索引
1
show index from compliance_page_info;
创建联合唯一性索引
1
2
3
4
5
6
7
alter table compliance_page_info add unique index unique_index_name(uuid, type);
alter table compliance_page_info add unique unique_index_name(uuid, type);
alter table compliance_page_info add unique key unique_index_name(uuid, type);
alter table compliance_page_info add primary key unique_index_name(uuid, type);
create unique index unique_index_name on compliance_page_info(uuid, type);
# 创建唯一性索引时表中存在重复记录，删除重复记录后创建唯一性索引
alter ignore table compliance_page_info add unique unique_index_name(uuid, type);
创建全文索引
1
alter table compliance_page_info add fulltext(uuid);
删除索引
1
2
3
alter table compliance_page_info drop index table_unique_index;
drop index unique_index_name on compliance_page_info;
alter table compliance_page_info drop primary key;
当记录不存在时Insert，存在时update
1
2
3
4
insert into compliance_page_info (uuid, total_page, total_count, type) values ('d60bc0d38b7a36fba07b2b9e4177d9cf', 1, 10, 'OVERVIEW-JUDGEMENT')
ON DUPLICATE KEY UPDATE total_page = values(total_page), total_count = values(total_count);
# 使用replace，记录存在先删除再插入（故受影响的列为2条），不存在直接插入
replace into compliance_page_info (uuid, total_page, total_count, type) values ('d60bc0d38b7a36fba07b2b9e4177d9cf', 1, 10, 'OVERVIEW-JUDGEMENT')
查询数据库表结构
1
2
3
4
select column_name, column_type, is_nullable, column_key, column_default, extra, column_comment
from information_schema.columns
where table_schema = 'ent_compliance'  		#表所在数据库
  and table_name = 'third_page_info' ; 		#你要查的表
删除重复数据
1
2
3
4
# 必须多嵌套一层select否则MySQL报错
delete from update_log where id not in (
  select * from (select min(id) from update_log group by uuid, did_or_page, type) b
);
正则&字符长度查询
1
delete from update_log where LENGTH(did_or_page) > 30 or did_or_page REGEXP '[0-9]+';
将一个表中的字段更新到另一表中
1
2
3
4
update asset_certificate a, base_static_data b
  set a.certificate_type = b.code_value, a.certificate_authority = b.extend_value
where a.certificate_type_code = b.code_name
  and a.certificate_type in ('null', '-', '') or a.certificate_type is null;
关联更新
1
2
3
update old_product op, new_product np 
set np.category = op category
where op.name = np.name
分组查询最大ID数据
1
2
3
4
5
select a.* from component a, (
	select component_id, max(version) as version
    from component group by component_id
) b
where a.component_id = b.component_id and a.version = b.version

死锁排查

1
2
3
4
5
6
-- 查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;