如何更改多个列上的表列数据类型？

例如:

ALTER TABLE webstore.Store MODIFY COLUMN (
ShortName VARCHAR(100),
UrlShort VARCHAR(100)
);

但是上面的方法不起作用，我使用的是 MySql5.x

125009

小开

Use the following syntax:

  ALTER TABLE your_table
MODIFY COLUMN column1 datatype,
MODIFY COLUMN column2 datatype,
... ... ... ... ...
... ... ... ... ...

Based on that, your ALTER command should be:

  ALTER TABLE webstore.Store
MODIFY COLUMN ShortName VARCHAR(100),
MODIFY COLUMN UrlShort VARCHAR(100)

Note that:

There are no second brackets around the MODIFY statements.
I used two separate MODIFY statements for two separate columns.

This is the standard format of the MODIFY statement for an ALTER command on multiple columns in a MySQL table.

Take a look at the following: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html and Alter multiple columns in a single statement

最佳答案

ALTER TABLE can do multiple table alterations in one statement, but MODIFY COLUMN can only work on one column at a time, so you need to specify MODIFY COLUMN for each column you want to change:

ALTER TABLE webstore.Store
MODIFY COLUMN ShortName VARCHAR(100),
MODIFY COLUMN UrlShort VARCHAR(100);

Also, note this warning from the manual:

When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward.