Rename all tables and columns to lowercase in MySQL?-Mysql Tutorial-php.cn

Rename all tables and columns to lowercase in MySQL?

WBOY

Release： 2023-08-27 13:41:02

forward

695 people have browsed it

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

You can do this with the help of INFORMATION_SCHEMA.COLUMNS. The syntax is as follows -

SELECT CONCAT(&#39;ALTER TABLE &#39;, TABLE_NAME, &#39; CHANGE `&#39;, COLUMN_NAME, &#39;` `&#39;,
LOWER(COLUMN_NAME), &#39;` &#39;, COLUMN_TYPE, &#39;;&#39;) AS anyAliasName
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = &lsquo;yourDatabaseName&rsquo;;

Copy after login

Now use a database with two tables. The database name is as follows "bothinnodbandmyisam". The database has the following tables -

employee
student

employee table description is as follows -

mysql> desc employee;

Copy after login

The following is the output. Assume that the following columns in the employee table are not lowercase -

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

Copy after login

The description of the student table is as follows. The query is as follows -

mysql> desc student;

Copy after login

The following is the output. Suppose the students table has the following columns which are not lowercase -

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

Copy after login

This is the query to change the column names of all tables to lowercase. The query is as follows -

mysql> SELECT CONCAT(&#39;ALTER TABLE &#39;, TABLE_NAME, &#39; CHANGE `&#39;, COLUMN_NAME, &#39;` `&#39;,
   -> LOWER(COLUMN_NAME), &#39;` &#39;, COLUMN_TYPE, &#39;;&#39;) AS changeColumnNameToLower
   -> FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = &#39;bothinnodbandmyisam&#39;;

Copy after login

The following is the output of the ALTER TABLE command showing the updated column names -

+------------------------------------------------------------------------+
| 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)

Copy after login

Looking at the above example output, all the column names have been changed to lowercase.

The above is the detailed content of Rename all tables and columns to lowercase in MySQL?. For more information, please follow other related articles on the PHP Chinese website!