在 MySQL 中将所有表和列重命名为小写？

您可以借助 INFORMATION_SCHEMA.COLUMNS 来实现此目的。语法如下 -

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `', LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS anyAliasName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘yourDatabaseName’;登录后复制

• employee
• student

employee 表的描述是如下 -

mysql> desc employee;登录后复制

+--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | EmployeeId | int(11) | YES | | NULL | | | EmployeeName | varchar(30) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)登录后复制

mysql> desc student;登录后复制

+-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | StudentId | int(11) | YES | | NULL | | | StudentName | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)登录后复制

mysql> SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `', -> LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS changeColumnNameToLower -> FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bothinnodbandmyisam';登录后复制

+------------------------------------------------------------------------+ | changeColumnNameToLower | +------------------------------------------------------------------------+ | ALTER TABLE employee CHANGE `EmployeeId` `employeeid` int(11); | | ALTER TABLE employee CHANGE `EmployeeName` `employeename` varchar(30); | | ALTER TABLE student CHANGE `StudentId` `studentid` int(11); | | ALTER TABLE student CHANGE `StudentName` `studentname` varchar(20); | +------------------------------------------------------------------------+ 4 rows in set (0.00 sec)登录后复制

以上就是在 MySQL 中将所有表和列重命名为小写？的详细内容，更多请关注每日运维网(www.mryunwei.com)其它相关文章！