MySQL 常用开发技巧

MySQL 常用开发技巧

整理常用的 MySQL 开发技巧。

修改自增值

ALTER TABLE [NAME] AUTO_INCREMENT = 100;

正则查询大写字母

SELECT * FROM [TABLE] WHERE BINARY [FIELD] REGEXP '[A-Z]'

行列转换

1. 行转列

测试数据
表名：student_score

SQL 语句

SELECT
	name,
	SUM( CASE course WHEN 'Chinese' THEN score END ) AS 'Chinese',
	SUM( CASE course WHEN 'English' THEN score END ) AS 'English',
	SUM( CASE course WHEN 'Math' THEN score END ) AS 'Math' 
FROM
	student_score
GROUP BY
	name;

转换结果

2. 列转行

测试数据
表名：permisson

表名：sequence

SQL 语句

SELECT
	user,
	REPLACE (
		SUBSTRING(
			SUBSTRING_INDEX( auth, ',', a.id ),
			CHAR_LENGTH( SUBSTRING_INDEX( auth, ',', a.id - 1 ) ) + 1 
		),
		',',
		'' 
	) AS auth 
FROM
	`sequence` a
	CROSS JOIN (
	SELECT
		user,
		CONCAT( auth, ',' ) AS auth,
		LENGTH( auth ) - LENGTH( REPLACE ( auth, ',', '' ) ) + 1 size 
	FROM
	`permission` 
	) b ON a.id <= b.size;

转换结果

删除重复数据

测试数据
表：user

SQL 语句
利用 GROUP BY 和 HAVING

SELECT
	name,
	COUNT(*) AS '重复次数' 
FROM
	`user` 
GROUP BY
name 
HAVING
	COUNT(*) > 1;

删除重复数据，保留 ID 最大的

DELETE a 
FROM
	`user` a
	JOIN (
	SELECT
		name,
		COUNT(*),
		MAX(id) AS id 
	FROM
	`user` 
	GROUP BY
	name 
	HAVING
		COUNT(*) > 1 
	) b ON a.name = b.name 
WHERE
	a.id < b.id

测试结果