mysql> show columns from cdr; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | id | int(10) | YES | | NULL | | | userfield | varchar(100) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)mysql> alter table cdr modify id int unsigned; Query OK, 7 rows affected (0.14 sec) Records: 7 Duplicates: 0 Warnings: 0mysql> show columns from cdr; +-----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | YES | | NULL | | | userfield | varchar(100) | YES | | NULL | | +-----------+------------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)这样?
这种语法上的问题你可以直接到手册中查一下。13.1.2. ALTER TABLE语法 ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...alter_specification: ADD [COLUMN] column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (column_definition,...) | ADD INDEX [index_name] [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] [index_type] (index_col_name,...) | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...) | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition] | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP INDEX index_name | DROP FOREIGN KEY fk_symbol | DISABLE KEYS | ENABLE KEYS | RENAME [TO] new_tbl_name | ORDER BY col_name | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | table_options | partition_options | ADD PARTITION partition_definition | DROP PARTITION partition_names | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | ANALYZE PARTITION partition_names | CHECK PARTITION partition_names | OPTIMIZE PARTITION partition_names | REBUILD PARTITION partition_names | REPAIR PARTITION partition_namesALTER TABLE用于更改原有表的结构。例如,您可以增加或删减列,创建或取消索引,更改原有列的类型,或重新命名列或表。您还可以更改表的评注和表的类型。允许进行的变更中,许多子句的语法与CREATE TABLE中的子句的语法相近。其中包括table_options修改,选项有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH等。请见13.1.5节,“CREATE TABLE语法”。存储引擎不支持有些操作,如果进行这些操作,会出现警告。使用SHOW WARNINGS可以显示出这些警告。请参见13.5.4.22节,“SHOW WARNINGS语法”。如果您使用ALTER TABLE更改列规约,但是DESCRIBE tbl_name提示您列规约并没有改变,则可能是因为MySQL忽略了您所做的更改。忽略更改的原因见13.1.5.1节,“沉寂的列规格变更”。例如,如果您试图把VARCHAR列更改为CHAR列,此时,如果表包含其它长度可变的列,则MySQL仍会使用VARCHAR。ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。注意,如果您在执行ALTER TABLE时使用除了RENAME以外的选项,则MySQL会创建一个临时表。即使数据并不需要进行复制(例如当您更改列的名称时),MySQL也会这么操作。对于MyISAM表,您可以通
alter <table> modify <column> int unsigned;关于unsigned int类型,可以看看它的帮助:INT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| userfield | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> alter table cdr modify id int unsigned;
Query OK, 7 rows affected (0.14 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql> show columns from cdr;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
| userfield | varchar(100) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)这样?
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| partition_options
| ADD PARTITION partition_definition
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_namesALTER TABLE用于更改原有表的结构。例如,您可以增加或删减列,创建或取消索引,更改原有列的类型,或重新命名列或表。您还可以更改表的评注和表的类型。允许进行的变更中,许多子句的语法与CREATE TABLE中的子句的语法相近。其中包括table_options修改,选项有ENGINE, AUTO_INCREMENT和AVG_ROW_LENGTH等。请见13.1.5节,“CREATE TABLE语法”。存储引擎不支持有些操作,如果进行这些操作,会出现警告。使用SHOW WARNINGS可以显示出这些警告。请参见13.5.4.22节,“SHOW WARNINGS语法”。如果您使用ALTER TABLE更改列规约,但是DESCRIBE tbl_name提示您列规约并没有改变,则可能是因为MySQL忽略了您所做的更改。忽略更改的原因见13.1.5.1节,“沉寂的列规格变更”。例如,如果您试图把VARCHAR列更改为CHAR列,此时,如果表包含其它长度可变的列,则MySQL仍会使用VARCHAR。ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。注意,如果您在执行ALTER TABLE时使用除了RENAME以外的选项,则MySQL会创建一个临时表。即使数据并不需要进行复制(例如当您更改列的名称时),MySQL也会这么操作。对于MyISAM表,您可以通