Modifying
Tables with ALTER TABLE
Introduction
You'll
probably find it necessary on occasion to redesign some of your
tables. A change in an application's specification may require that
you record information not accounted for in the original definition
of a table used by that application. Or you may find that an
AUTO_INCREMENT
column
is running out of room to generate new sequence numbers and you need
to change the column to use a larger integer type. MySQL offers many
possibilities for modifying a table's structure. This chapter
describes how to make the following types of changes:
·
Dropping,
adding, or repositioning a column.
Columns
that have become unnecessary or that you discover to be redundant may
be removed to simplify a table and to save space. Or you may move
columns from one table to another as part of a normalization
procedure. Columns may be added when you need to record additional
types of information.
·
Changing
a column definition or name.
If
a column as originally created does not serve your purposes, you may
be able to correct the problem by redefining it. For example, you can
convert a string column that is case sensitive to one that is not, or
vice versa. Or you may have an AUTO_INCREMENT
column
that is a TINYINT
and
has room only for 127 sequence values. By changing the column to be
unsigned or to use a larger integer type, you can extend the range of
the sequence. Renaming a column can be useful if after an upgrade to
a more recent version of MySQL you find that a column name is now a
reserved word. Or maybe you just want to rename a column like num
to
something more descriptive like test_score
to
make the column's purpose more explicit.
·
Changing
a table's type.
The
various table types in MySQL have differing characteristics. If a
table's type is less suitable for your applications than another
type, you can convert it.
·
Renaming
a table.
Like
renaming a column, this can be done if you come up with a better
name. Or you can rename for other purposes such as rotating the names
of a set of tables used for logging.
·
Modifying
a table's index structure
Dropping
an index that is rarely used can improve performance of inserts and
updates of table rows, because that index then need not be updated.
Adding an index to a column that you reference frequently in queries
can be useful for improving SELECT
performance.
Indexing can also be used to remove duplicate values from a table.
1,
Dropping, Adding, or Repositioning a Column
Use
the DROP
or
ADD
clauses
of ALTER
TABLE to
remove or add a column. To move a column, drop it and then put it
back where you want it.
mysql>
SHOW
COLUMNS FROM mytbl;
mysql>
DESC mytbl;
//上面两句的作用都是一样的，显示表的结构
ALTER
TABLE mytbl DROP i;
//删除表的一列
ALTER
TABLE mytbl ADD i INT;
//给表增加一列,该列排在最后
ALTER
TABLE mytbl DROP i;
ALTER
TABLE mytbl ADD i INT FIRST;
//给表增加一列,该列排在最前
ALTER
TABLE mytbl DROP i;
ALTER
TABLE mytbl ADD i INT AFTER c;
//给表增加一列,该列排在c列后面
问题：当表已经确立后，该如何调整其排列顺序？？
2,
Changing a Column Definition or Name
Use
MODIFY
or
CHANGE.
MODIFY
is
simpler, but cannot change the column name. CHANGE
is more
confusing to use, but can change both the name and the definition.
ALTER
TABLE tbl_name
MODIFY
col_name
...
;
ALTER
TABLE tbl_name
CHANGE
col_name
col_name ...
;
It
would be nice to have a form of the ALTER
TABLE statement
that renamed a column without the need to repeat the definition,
especially for working with ENUM
and
SET
columns
that have many member values. Unfortunately, there is no such
statement, which makes these column types somewhat difficult to work
with when using ALTER
TABLE.
One
way to avoid retyping the definition is to capture the current
definition in a file and edit the file to produce the proper ALTER
TABLE statement:
·
Run
mysqldump
to
get the CREATE
TABLE statement
that contains the column definition:
%
mysqldump
--no-data cookbook mytbl > test.txt
·
Edit
the test.txt
file
to remove everything but the definition for the e
column.
·
Modify
the definition to produce an ALTER
TABLE statement
with a semicolon at the end.
·
Write
test.txt
back
out to save it, then get out of the editor and feed test.txt
as
a batch file to mysql.
%
mysql
cookbook < test.txt
3,
The Effect of ALTER TABLE on Null and Default Value Attributes
You
changed a column definition, but MySQL modified the column's NULL
value
and default value attributes when you didn't tell it to.
Those
attributes are part of the column definition. If you don't specify
them explicitly, MySQL chooses their values for you. So just be more
specific about how you want the column defined.
mysql>
ALTER
TABLE mytbl MODIFY j INT NOT NULL DEFAULT 100;
mysql>
SHOW
COLUMNS FROM mytbl LIKE 'j';
对默认值的处理：
mysql>
ALTER
TABLE mytbl MODIFY j BIGINT;
mysql>
SHOW
COLUMNS FROM mytbl LIKE 'j';
mysql>
ALTER
TABLE mytbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql>
SHOW
COLUMNS FROM mytbl LIKE 'j';
4,
Changing a Column's Default Value
Use
SET
DEFAULT to
specify the default value explicitly, or DROP
DEFAULT to
remove the current default and allow MySQL to assign the "default
default."
To
change a default value, use ALTER
col_name
SET
DEFAULT:
ALTER
TABLE mytbl ALTER j SET DEFAULT 1000;
To
drop a default value, use ALTER
col_name
DROP
DEFAULT:
ALTER
TABLE mytbl ALTER j DROP DEFAULT;
5,
Changing a Table Type
Use
ALTER
TABLE to
change its type with a TYPE
clause.
To
find out the current type of a table, use the SHOW
TABLE STATUS statement
(introduced in MySQL 3.23.0) or SHOW
CREATE TABLE (introduced
in MySQL 3.23.20):
mysql>
SHOW
TABLE STATUS LIKE 'mytbl'\G
mysql>
SHOW
CREATE TABLE mytbl\G
Changing
a table type is easy; use ALTER
TABLE with
a TYPE
specifier.
For example, to convert a table to the MyISAM type, use this
statement:
ALTER
TABLE tbl_name
TYPE
= MYISAM;
6,
Renaming a Table
To
rename a table, use the RENAME
option
of the ALTER
TABLE statement:
ALTER
TABLE old_name
RENAME
TO new_name;
As
of Version 3.23.23, MySQL includes an explicit RENAME
TABLE statement:
RENAME
TABLE old_name
TO
new_name;
RENAME
TABLE allows
you to rename multiple tables, which allows you to do things such as
swap the names of two tables in a single statement:
RENAME
TABLE name1
TO
temp_name,
name2
TO
name1,
tmp_name
to
name2;
You
can achieve the same result with ALTER
TABLE,
except that you need three separate statements. Because of that, the
tables become available to other clients in the brief intervals
between statements, which may be undesirable. Using a single RENAME
TABLE statement
avoids this problem.
7,
Adding or Dropping Indexes
ALTER
TABLE can
not only drop or add columns, it can drop or add indexes on those
columns. These operations often are useful for improving the
performance of a database. Typically, indexing a column that you
query frequently helps SELECT
statements
run faster because the index allows MySQL to avoid full table scans.
Dropping indexes can sometimes be useful as well. Whenever a row is
modified, MySQL must update any indexes that include the modified
columns. If you don't actually use a particular index very much, it's
possible that your table is overindexed and that dropping the index
will speed up performance of table updates.
mysql>
SHOW
INDEX FROM mytbl;
There
are four types of statements for adding indexes to a table:
ALTER
TABLE tbl_name
ADD
PRIMARY KEY (column_list);
ALTER
TABLE tbl_name
ADD
UNIQUE index_name
(column_list);
ALTER
TABLE tbl_name
ADD
INDEX index_name
(column_list);
ALTER
TABLE tbl_name
ADD
FULLTEXT index_name
(column_list);
The
first statement adds a PRIMARY
KEY,
which means that indexed values must be unique and cannot be NULL.
The second creates an index for which values must be unique (with the
exception of NULL
values,
which may appear multiple times). The third adds an ordinary index in
which any value may appear more than once. The fourth creates a
special FULLTEXT
index
that is used for text-searching purposes.
Dropping
a PRIMARY
KEY is
easiest, because you need not know the index name:
ALTER
TABLE mytbl DROP PRIMARY KEY;
To
drop an index that is not a PRIMARY
KEY,
you must specify the index name. If you don't know the name, use SHOW
INDEX.
Vertical-format output (specified by \G)
often is useful with this statement, to avoid long line wraparound:
mysql>
SHOW
INDEX FROM mytbl\G
The
Key_name
and
Seq_in_index
values
show the index names and the positions of columns within an index.
8,
Eliminating Duplicates by Adding an Index
One
way to do this is to create a unique index on the column or columns
containing duplicates.
If
MySQL discovers duplicate key values when you try to create a PRIMARY
KEY or
a UNIQUE
index,
it aborts the ALTER
TABLE operation.
To ignore the duplicates and proceed anyway, use ALTER
IGNORE TABLE rather
than ALTER
TABLE.
The IGNORE
keyword
tells MySQL to retain the first row containing a duplicated key value
and discard the others. This is, in fact, a useful way to eliminate
duplicates in a column or set of columns: just create a unique-valued
index and let MySQL throw away the duplicates.
INSERT
INTO client_info (id,name,address)
SELECT
id,name,address FROM client_billing;
INSERT
INTO bill_item (id,date,minutes,description)
SELECT
id,date,minutes,description FROM client_billing;