Home > Database > Mysql Tutorial > body text

Rename all tables and columns to lowercase in MySQL?

WBOY
Release: 2023-08-27 13:41:02
forward
736 people have browsed it

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

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

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS anyAliasName
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘yourDatabaseName’;
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('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
   -> LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS changeColumnNameToLower
   -> FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bothinnodbandmyisam';
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!

source:tutorialspoint.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template