使用ALTER时MySQL未知列错误，不了解行为 [英] MySQL unknown column error when using ALTER, don't understand behaviour

问题描述

我想知道是否有人可以帮助我.

I was wondering if someone could help me.

发出ALTER命令时，我的行为很奇怪.该命令来自MySQL Workbench sync，失败.我有一个带有字段的表:

I have a odd behaviour while issueing a ALTER command. The command comes from MySQL Workbench sync and it is failing. I have a table with fields:

`id`  int(11) NOT NULL AUTO_INCREMENT ,
`text`  varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
`updated`  datetime NULL DEFAULT NULL ,
`remote_addr`  varchar(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`http_user_agent`  varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`user_id`  int(11) NULL DEFAULT NULL ,
`category`  varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ,
`created`  datetime NULL DEFAULT NULL ,
PRIMARY KEY (`id`)

我想发出ALTER命令:

And I want to issue the ALTER command:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

我得到回应:

Unknown column 'created' in 'logs'

但是

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`

本身可以工作，并且:

ALTER TABLE `logs`
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

也可以单独工作.

我不明白为什么当在一个查询中将两者组合在一起时却不起作用，并说创建的"不存在.我知道它肯定存在.

I don't understand why when both are combined in one query it doesn't work and says that 'created' doesn't exist. I know that it definately exists.

请注意，我不担心'created'的更改列，它是由MWB在比较和准备同步时生成的.但是只是想知道为什么不能同时对一个查询执行这两个操作.

Note that I'm not worried about the change column for 'created', it is generated by MWB when comparing and preparing to sync. But was just wondering why both actions can't be put on one query.

我正在使用MySQL 5.5.8

I am using MySQL 5.5.8

更新

我实际上可以执行多个子句.我一直在其他桌子上做得很好.

I actually can do multiple clauses okay. I have been doing it on other tables just fine.

我忘了提这个.但是，当我删除AFTER部分后，它就可以工作了.

I forgot to mention this. But when I remove the AFTER part it works.

所以这不起作用:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

但是确实如此:

ALTER TABLE `logs`
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL,
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`

推荐答案

我遇到了同样的问题.我通过在添加列之前执行CHANGE COLUMN(或MODIFY COLUMN)来解决此问题.

I had the same problem. I solved it by doing the CHANGE COLUMN (or MODIFY COLUMN) before ADD COLUMN.

在您的示例中，将给出以下SQL语句:

In your example that would give the following SQL statement :

ALTER TABLE `logs`
CHANGE COLUMN `created` `created` DATETIME NULL DEFAULT NULL AFTER `category`,
ADD COLUMN `updated` DATETIME NULL DEFAULT NULL AFTER `created`;

这篇关于使用ALTER时MySQL未知列错误，不了解行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！