重命名索引列

发布于 2024-09-03 09:41:11 字数 378 浏览 6 评论 0原文

我有一个包含大约 40 个表的数据库，需要重命名每个索引列。

IE USER 一个表有一堆字段，就像

user_id | user_username | user_password | etc...

我想将 ID 列重命名为 id ie

id | user_username | user_password | etc...

但我在 alter table 命令上不断收到 mysql 错误。

ALTER TABLE 数据库将列 user_id 重命名为 id；

还有许多不同的变体。

最好的方法是什么？

希望大家多多指教

原文

I have a database with around 40 tables and need to rename every index column.

IE USER a table has a bunch of fields like

user_id | user_username | user_password | etc...

I want to rename the ID columns just to id ie

id | user_username | user_password | etc...

But I keep getting mysql errors on the alter table command ie.

ALTER TABLE database RENAME COLUMN user_id to id;

Plus many different variations.

Whats the best way to do this ?

Hope you can advise

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

执笔绘流年 2024-09-10 09:41:12

您应该能够使用如下代码为每个表执行此操作（注意：我假设每个 *_id 字段的列描述为 INTEGER NOT NULL 并且这些相同的字段设置了 AUTO_INCRMENT ，这对于 ID 列来说是很正常的。）

# remove auto_increment, allows us to remove primary key
ALTER TABLE user MODIFY user_id INTEGER NOT NULL;

# remove primary key, allows us to change column name
ALTER TABLE user DROP PRIMARY KEY;

# change column name, re-add auto_increment and primary key
ALTER TABLE user CHANGE user_id id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY;

不过，请注意其他人的警告。除非这是您的创建（并且您刚刚决定在项目或其他内容的早期更改命名约定），否则更改此设置将破坏期望与此数据库交互的任何其他代码。

希望这有帮助，

安东尼

编辑：上面假设您没有外键约束，其中其他表显式引用 user.user_id 列。如果不是这种情况，您将必须在更改名称之前删除所有外键约束，然后重新添加它们。如果您不确定数据库中哪里存在外键关系，您可以使用以下代码列出所有外键关系（复制自 http://www.xinotes.org/notes/note/477/ 以提高答案的复制/粘贴能力）：

SELECT CONCAT(table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name)
FROM information_schema.key_column_usage
WHERE referenced_table_schema = schema()
AND referenced_table_name is not null
ORDER BY table_name, column_name;

You should be able to do it, for each table, with code like the following (Note: I assume that the column description for each *_id field is INTEGER NOT NULL and that those same fields have AUTO_INCREMENT set. This is pretty normal for an ID column.)

# remove auto_increment, allows us to remove primary key
ALTER TABLE user MODIFY user_id INTEGER NOT NULL;

# remove primary key, allows us to change column name
ALTER TABLE user DROP PRIMARY KEY;

# change column name, re-add auto_increment and primary key
ALTER TABLE user CHANGE user_id id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY;

Heed the other guys' warnings, though. Unless this is your creation (and you've just decided to change naming convention early on in the project or something), changing this will break any other code that expects to interface with this database.

Hope this helps,

Anthony

EDIT: the above assumes that you have no foreign key constraints, where other tables explicitly reference the user.user_id column. If this is not the case, you will have to remove the all of the foreign key constraints before changing the name, then re-add them afterwards. If you're unsure where there are foreign key relationships in your database, you can list them all with the following code (copied from http://www.xinotes.org/notes/note/477/ to improve copy/paste-ability of answer):

SELECT CONCAT(table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name)
FROM information_schema.key_column_usage
WHERE referenced_table_schema = schema()
AND referenced_table_name is not null
ORDER BY table_name, column_name;

回复收藏 0 原文