mysql - 是否可以更新 information_schema 表以便将前导文本添加到列名中？

Question

我正在尝试以 root 身份执行此 SQL:

UPDATE information_schema.COLUMNS SET COLUMN_NAME = CONCAT('emp',COLUMN_NAME)  WHERE TABLE_SCHEMA = 'edsdb'

但是得到如下错误:

SQL Error [1044] [42000]: Access denied for user 'root'@'localhost' to database 'information_schema'
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'root'@'localhost' to database 'information_schema'

我需要“修复”列名称的这些表中有很多列。是否可以直接在 SQL 中执行此操作？

Answer 1

根据 MySQL 文档 (here)，information_schema 包含只读表，它们是 View ，因此您无法更改它:

The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.

您需要使用 ALTER TABLE 命令来代替，例如:

ALTER TABLE `edsdb` CHANGE `id` `empid` INT;