select concat('alter table ', table_name, ' change `', column_name, '` `',lower(column_name), '` ', column_type, ';') as anyaliasnamefrom information_schema.columns where table_schema = ‘yourdatabasename’;
现在使用有两个表的数据库。数据库名称如下“bothinnodbandmyisam”。该数据库有以下表格 -
employeestudentemployee 表的描述是如下 -
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';
以下是显示 alter table 命令的输出，其中显示了更新的列名称 -
+------------------------------------------------------------------------+| 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 中将所有表和列重命名为小写？的详细内容。