时间差

  • 类型
秒数 分钟数 小时数 天数 周数 季度数 月数 年数
SECOND MINUTE HOUR DAY WEEK QUARTER MONTH YEAR
  • 举例
1
SELECT TIMESTAMPDIFF(SECOND,‘1993-03-23 00:00:00’,DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
  • 做时间减法
1
SELECT DATE_SUB(NOW(), INTERVAL 8 HOUR); ---少8个小时

补位

  • 前后补字符
1
2
select RPAD('1',8,'0') as num
select LPAD('1',8,'0') as num

常用函数

  • 返回参数列表中的第一个非空值，如果所有参数都是NULL，则返回NULL
1
2
SELECT COALESCE(NULL, 1, 2); -- 返回 1
SELECT COALESCE(NULL, NULL, NULL); -- 返回 NULL

行拆分

  • 对于逗号隔开的数据拆分成行
1
2
3
4
5
6
SELECT
    substring_index(substring_index( a.note,',',b.help_topic_id + 1),',' ,- 1)
FROM
    (select '1,2,3,4' as note) a  
JOIN mysql.help_topic b ON b.help_topic_id <
(length(a.note) - length( replace(a.note,',','')) + 1)
  • 变形，补行
1
2
3
4
5
6
SELECT
    a.startNum + b.help_topic_id AS rn
FROM
    (select '001' AS startNum,'200' as endNumber) a  
JOIN mysql.help_topic b ON b.help_topic_id <
(a.endNumber -a.startNum   + 1)
  • 结合上面的行拆分
1
2
3
4
5
6
SELECT
    LPAD(a.startNum + b.help_topic_id,3,'0') AS rn
FROM
    (select '001' AS startNum,'200' as endNumber) a  
JOIN mysql.help_topic b ON b.help_topic_id <
(a.endNumber -a.startNum   + 1)

逗号隔开的数据关联查询

table2的PARAM_ID是逗号隔开的，要查询显示逗号隔开的table2.PARAM_NAME

1
2
3
4
5
6
SELECT
	GROUP_CONCAT( table2.PARAM_NAME ) 
FROM
	table2 
WHERE
	table2.PARAM_ID REGEXP ( SELECT REPLACE ( ( SELECT table1.PARAM_ID FROM table1 WHERE table1.id = '1' ), ',', '|' ) )

行转列

  • 逗号隔开的数据
1
2
3
4
5
6
SELECT
	SUBSTRING_INDEX( SUBSTRING_INDEX( '1,2,3,4', ',', help_topic_id + 1 ), ',',- 1 ) AS num 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < LENGTH( '1,2,3,4' ) - LENGTH( REPLACE ( '1,2,3,4', ',', '' ) ) +1
  • 非逗号隔开的数据
1
SELECT LEFT(SUBSTRING('QWER',help_topic_id+1),1) AS num FROM mysql.help_topic WHERE help_topic_id < LENGTH('QWER');

常用语法

获取某个表的某个字段信息

1
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='表名' and COLUMN_NAME='列名'

参数连接

1
select CONCAT(A, B, C) from table

参数连接用concat函数

注：存在复杂字符串连接，若其中某个字段查询为null，则整个字符串为null(解决办法为通过IFNULL函数判断，IFNULL(AAA,’’))

查看执行计划

explain 字段

  • 查看mysql的耗cpu
1
select * from performance_schema.threads where PROCESSLIST_COMMAND = 'Query' order by THREAD_OS_ID DESC

注：THREAD_OS_ID越高用的线程数越高

  • 查看目前正在查询的耗时时间长的SQL
1
select * from information_schema.`PROCESSLIST` where Command != 'Sleep' order by `time` DESC
  • 查看进程
1
SHOW PROCESSLIST;
  • 统一修改字符集(生产修改的sql然后统一执行)
1
2
3
4
5
6
select 
distinct
CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;") 
from information_schema.COLUMNS where TABLE_SCHEMA = 'fit'  and table_name like 't_pom%' 
and (character_set_name != 'utf8'
or collation_name != 'utf8_general_ci')
  • 统一添加索引
1
2
3
4
select 
distinct
CONCAT("ALTER TABLE `", TABLE_NAME,"`  ADD INDEX `IX_DELETED` USING BTREE(`IS_DELETED`);") 
from information_schema.COLUMNS where TABLE_SCHEMA = 'hra'
  • 删除所有索引
1
2
3
4
select 
distinct
CONCAT("DROP INDEX `IX_DELETED` on ", TABLE_NAME,";") 
from information_schema.COLUMNS where TABLE_SCHEMA = 'hra_vn1' and table_name like 'm_wms%'
  • 查询所有的索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
CONCAT('ALTER TABLE `',TABLE_NAME,'` ', 'ADD ', 
 IF(NON_UNIQUE = 1,
 CASE UPPER(INDEX_TYPE)
 WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
 WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
 ELSE CONCAT('INDEX `',
  INDEX_NAME,
  '` USING ',
  INDEX_TYPE
 )
END,
IF(UPPER(INDEX_NAME) = 'PRIMARY',
 CONCAT('PRIMARY KEY USING ',
 INDEX_TYPE
 ),
CONCAT('UNIQUE INDEX `',
 INDEX_NAME,
 '` USING ',
 INDEX_TYPE
)
)
),'(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'aps_vn1' and TABLE_NAME like 't_pom%'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC
  • 查询缓存
1
2
3
4
5
#quert_cache_type 0-关闭 1-开启 2-查询使用SQL_CACHE的才缓存 
#linux my.cnf文件
query_cache_type=0
#使用方法(一般对于静态表才使用缓存)
SELECT SQL_CACHE * FROM test where id = 1
  • 正在查询的慢sql
1
select *  from information_schema.processlist where command='query' and time > 20;
  • 查询慢sql TO kill
1
select concat('kill ', id,';') from information_schema.processlist where time > 20;