关于MySql使用alter的问题

测试了一下, 好像无法让默认值为 日期 / 时间的.

仅仅有一种特殊情况下可以.

ALTER TABLE money

MODIFY COLUMN

time TIMESTAMP NOT NULL

DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

但是麻烦的地方在于:

上面的写法,默认值是可以成为当前日期时间了,但是好像

ON UPDATE CURRENT_TIMESTAMP 会导致 “更新的时候，也会更新这个字段。”

mysql里面的man功能使用help命令，你可以输入下面的命令：

help

help alter

help alter table

help命令显示使用帮助的几本方法：

mysql>help

For information about MySQL products and services, visit:

http://www.mysql.com/

For developer information, including the MySQL Reference Manual, visit:

http://dev.mysql.com/

To buy MySQL Network Support, training, or other products, visit:

https://shop.mysql.com/

List of all MySQL commands:

Note that all text commands must be first on line and end with ''

? (\?) Synonym for `help'.

clear (\c) Clear command.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new

delimiter.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go(\g) Send command to mysql server.

help (\h) Display this help.

notee (\t) Don't write into outfile.

print (\p) Print current command.

prompt(\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash(\#) Rebuild completion hash.

source(\.) Execute an SQL script file. Takes a file name as an argument.

status(\s) Get status information from the server.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog

with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

help alter命令查看alter的几本功能

mysql>help alter

Many help items for your request exist.

To make a more specific request, please type 'help <item>',

where <item>is one of the following

topics:

ALTER DATABASE

ALTER PROCEDURE

ALTER TABLE

ALTER VIEW

GRANT

SPATIAL

help alter table查看alter table的详细解释

mysql>help alter table

Name: 'ALTER TABLE'

Description:

Syntax:

ALTER [IGNORE] TABLE tbl_name

alter_specification [, alter_specification] ...

alter_specification:

table_option ...

| ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]

| ADD [COLUMN] (col_name column_definition,...)

| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

PRIMARY KEY [index_type] (index_col_name,...)

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [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 new_col_name column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP {INDEX|KEY} index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col_name [, col_name] ...

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH | RTREE}

ALTER TABLE enables you to change the structure of an existing table.

For example, you can add or delete columns, create or destroy indexes,

change the type of existing columns, or rename columns or the table

itself. You can also change the comment for the table and type of the

table.

URL: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

将表格修改为下面第二个表格形式。

用一条ALTER语句实现。

CHANGE:如果我们不只是修改单一列，而是用一条语句改变两个列，我们需要修改列的名称，同时更改他们的数据类型，这时就需要我们用到关键字CHANGE,可以在一条语句中放入多个CHANGE,在中间加上分隔的逗号即可。

比如原来的表两列，类型为VARCHAR(50),和VARCHAR(10),现在不止要更改列名，还要更改这两列的类型。

程序如下：

MODIFY:使用它可以只修改列的类型而不会干涉它的名称，假设要把proj_desc列的字符长度修改为VARCHAR(120)以容纳更多的说明文字，只要这么做就可以。

总结如下

(1)既更改列名也更改类型，用CHANGE

(2)只修改类型，用MODIFY,但这种方法比较慢，我们用另一种方法直接修改.frm文件，而不改动表本身。

---