Oracle 5.0, Справочное руководство

Страница: 1039 / 3232

ALTER TABLE
Syntax
1019
character set (like
utf8
). In this case, you have to do the following for each
such column:
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The reason this works is that there is no conversion when you convert to or
from
BLOB
columns.
To change only the default character set for a table, use this statement:
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name ;
The word
DEFAULT
is optional. The default character set is the character set that is used if you
do not specify the character set for columns that you add to a table later (for example, with
ALTER
TABLE ... ADD column
).
With the
mysql_info()
C API function, you can find out how many rows were copied by
ALTER
TABLE
, and (when
IGNORE
is used) how many rows were deleted due to duplication of unique key
values. See Section 20.6.6.35, “
mysql_info()
” .
13.1.4.1.
ALTER TABLE
Examples
Begin with a table
t1
that is created as shown here:
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from
t1
to
t2
:
ALTER TABLE t1 RENAME t2;
To change column
a
from
INTEGER
to
TINYINT NOT NULL
(leaving the name the same), and to
change column
b
from
CHAR(10)
to
CHAR(20)
as well as renaming it from
b
to
c
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new
TIMESTAMP
column named
d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column
d
and a
UNIQUE
index on column
a
:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
To remove column
c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new
AUTO_INCREMENT
integer column named
c
:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (c);
Note that we indexed
c
(as a
PRIMARY KEY
) because
AUTO_INCREMENT
columns must be indexed,
and also that we declare
c
as
NOT NULL
because primary key columns cannot be
NULL
.
When you add an
AUTO_INCREMENT
column, column values are filled in with sequence numbers
automatically. For
MyISAM
tables, you can set the first sequence number by executing
SET
INSERT_ID=value
before
ALTER TABLE
or by using the
AUTO_INCREMENT=value
table option.
See Section 5.1.4, “Server System Variables” .
With
MyISAM
tables, if you do not change the
AUTO_INCREMENT
column, the sequence number is not
affected. If you drop an
AUTO_INCREMENT
column and then add another
AUTO_INCREMENT
column,
the numbers are resequenced beginning with 1.