alter在mysql_在MySQL如何使用Alter Table？

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 anAUTO_INCREMENTcolumn

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 anAUTO_INCREMENTcolumn

that is aTINYINTand

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 likenumto

something more descriptive liketest_scoreto

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 improvingSELECTperformance.

Indexing can also be used to remove duplicate values from a table.

1,

Dropping, Adding, or Repositioning a Column

Use

theDROPorADDclauses

ofALTER

TABLEto

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

UseMODIFYorCHANGE.MODIFYis

simpler, but cannot change the column name.CHANGEismore

confusing to use, but can change both the name and the definition.

ALTER

TABLEtbl_nameMODIFYcol_name...

;

ALTER

TABLEtbl_nameCHANGEcol_name

col_name...

;

It

would be nice to have a form of theALTER

TABLEstatement

that renamed a column without the need to repeat the definition,

especially for working withENUMandSETcolumns

that have many member values. Unfortunately, there is no such

statement, which makes these column types somewhat difficult to work

with when usingALTER

TABLE.

One

way to avoid retyping the definition is to capture the current

definition in a file and edit the file to produce the properALTER

TABLEstatement:

·Runmysqldumpto

get theCREATE

TABLEstatement

that contains the column definition:

%mysqldump

--no-data cookbook mytbl > test.txt

·Edit

thetest.txtfile

to remove everything but the definition for theecolumn.

·Modify

the definition to produce anALTER

TABLEstatement

with a semicolon at the end.

·Writetest.txtback

out to save it, then get out of the editor and feedtest.txtas

a batch file tomysql.

%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'sNULLvalue

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

UseSET

DEFAULTto

specify the default value explicitly, orDROP

DEFAULTto

remove the current default and allow MySQL to assign the "default

default."

To

change a default value, useALTERcol_nameSET

DEFAULT:

ALTER

TABLE mytbl ALTER j SET DEFAULT 1000;

To

drop a default value, useALTERcol_nameDROP

DEFAULT:

ALTER

TABLE mytbl ALTER j DROP DEFAULT;

5,

Changing a Table Type

UseALTER

TABLEto

change its type with aTYPEclause.

To

find out the current type of a table, use theSHOW

TABLE STATUSstatement

(introduced in MySQL 3.23.0) orSHOW

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; useALTER

TABLEwith

aTYPEspecifier.

For example, to convert a table to the MyISAM type, use this

statement:

ALTER

TABLEtbl_nameTYPE

= MYISAM;

6,

Renaming a Table

To

rename a table, use theRENAMEoption

of theALTER

TABLEstatement:

ALTER

TABLEold_nameRENAME

TOnew_name;

As

of Version 3.23.23, MySQL includes an explicitRENAME

TABLEstatement:

RENAME

TABLEold_nameTOnew_name;

RENAME

TABLEallows

you to rename multiple tables, which allows you to do things such as

swap the names of two tables in a single statement:

RENAME

TABLEname1TOtemp_name,name2TOname1,tmp_nametoname2;

You

can achieve the same result withALTER

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 singleRENAME

TABLEstatement

avoids this problem.

7,

Adding or Dropping Indexes

ALTER

TABLEcan

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 helpsSELECTstatements

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

TABLEtbl_nameADD

PRIMARY KEY (column_list);

ALTER

TABLEtbl_nameADD

UNIQUEindex_name(column_list);

ALTER

TABLEtbl_nameADD

INDEXindex_name(column_list);

ALTER

TABLEtbl_nameADD

FULLTEXTindex_name(column_list);

The

first statement adds aPRIMARY

KEY,

which means that indexed values must be unique and cannot beNULL.

The second creates an index for which values must be unique (with the

exception ofNULLvalues,

which may appear multiple times). The third adds an ordinary index in

which any value may appear more than once. The fourth creates a

specialFULLTEXTindex

that is used for text-searching purposes.

Dropping

aPRIMARY

KEYis

easiest, because you need not know the index name:

ALTER

TABLE mytbl DROP PRIMARY KEY;

To

drop an index that is not aPRIMARY

KEY,

you must specify the index name. If you don't know the name, useSHOW

INDEX.

Vertical-format output (specified by\G)

often is useful with this statement, to avoid long line wraparound:

mysql>SHOW

INDEX FROM mytbl\G

TheKey_nameandSeq_in_indexvalues

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 aPRIMARY

KEYor

aUNIQUEindex,

it aborts theALTER

TABLEoperation.

To ignore the duplicates and proceed anyway, useALTER

IGNORE TABLErather

thanALTER

TABLE.

TheIGNOREkeyword

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;

posted on 2007-12-12 19:55 kingsmiler 阅读(1343) 评论(0)  编辑  收藏