mysql批量改表名及列名

批量改表名前缀SQL

-- bm_为原有表名前缀，b2c_为目标表名前缀
SELECT
	CONCAT(
		'ALTER TABLE ',
		table_name,
		' RENAME TO b2c_',
		substring(table_name, 4),
		';'
	)
FROM
	information_schema. TABLES
WHERE
	table_schema = 'b2c_sass'
AND table_name LIKE 'bm_%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

批量改字段

-- uid为原字段，user_id为新字段
SELECT
	CONCAT(
		'ALTER TABLE ',
		table_name,
		' CHANGE COLUMN `uid` `user_id` bigint(20) NOT NULL DEFAULT 0 COMMENT "用户id" ;'
	)
FROM
	information_schema. TABLES
WHERE
	table_schema = 'b2cmall'
AND TABLE_NAME IN (
	SELECT
		TABLE_NAME
	FROM
		information_schema.`COLUMNS`
	WHERE
		TABLE_SCHEMA = 'b2cmall'
	AND COLUMN_NAME = 'uid'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21