alter_table.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

alter table t1 add unique (a,b), add key (b);
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	a	1	a	A	NULL	NULL	NULL	YES	BTREE	
t1	0	a	2	b	A	NULL	NULL	NULL	YES	BTREE	
t1	1	b	1	b	A	100	NULL	NULL	YES	BTREE	
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	a	1	a	A	3	NULL	NULL	YES	BTREE	
t1	0	a	2	b	A	300	NULL	NULL	YES	BTREE	
t1	1	b	1	b	A	100	NULL	NULL	YES	BTREE	
drop table t1;
CREATE TABLE t1 (i int(10), index(i) );
ALTER TABLE t1 DISABLE KEYS;
INSERT DELAYED INTO t1 VALUES(1),(2),(3);
ALTER TABLE t1 ENABLE KEYS;
drop table t1;
CREATE TABLE t1 (
Host varchar(16) binary NOT NULL default '',
User varchar(16) binary NOT NULL default '',
PRIMARY KEY  (Host,User)
) ENGINE=MyISAM;
ALTER TABLE t1 DISABLE KEYS;
LOCK TABLES t1 WRITE;
INSERT INTO t1 VALUES ('localhost','root'),('localhost',''),('games','monty');
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
ALTER TABLE t1 ENABLE KEYS;
UNLOCK TABLES;
CHECK TABLES t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
CREATE TABLE t1 (
Host varchar(16) binary NOT NULL default '',
User varchar(16) binary NOT NULL default '',
PRIMARY KEY  (Host,User),
KEY  (Host)
) ENGINE=MyISAM;
ALTER TABLE t1 DISABLE KEYS;
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
t1	1	Host	1	Host	A	NULL	NULL	NULL		BTREE	disabled
LOCK TABLES t1 WRITE;
INSERT INTO t1 VALUES ('localhost','root'),('localhost','');
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
t1	1	Host	1	Host	A	NULL	NULL	NULL		BTREE	disabled
ALTER TABLE t1 ENABLE KEYS;
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
t1	0	PRIMARY	2	User	A	2	NULL	NULL		BTREE	
t1	1	Host	1	Host	A	1	NULL	NULL		BTREE	
UNLOCK TABLES;
CHECK TABLES t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
LOCK TABLES t1 WRITE;
ALTER TABLE t1 RENAME t2;
UNLOCK TABLES;
select * from t2;
Host	User
localhost	
localhost	root
DROP TABLE t2;
CREATE TABLE t1 (
Host varchar(16) binary NOT NULL default '',
User varchar(16) binary NOT NULL default '',
PRIMARY KEY  (Host,User),
KEY  (Host)
) ENGINE=MyISAM;
LOCK TABLES t1 WRITE;
ALTER TABLE t1 DISABLE KEYS;
SHOW INDEX FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	Host	A	NULL	NULL	NULL		BTREE	
t1	0	PRIMARY	2	User	A	0	NULL	NULL		BTREE	
t1	1	Host	1	Host	A	NULL	NULL	NULL		BTREE	disabled
DROP TABLE t1;
create table t1 (a int);
alter table t1 rename to `t1\\`;
ERROR 42000: Incorrect table name 't1\\'
rename table t1 to `t1\\`;
ERROR 42000: Incorrect table name 't1\\'
drop table t1;
drop table if exists t1, t2;
Warnings:
Note	1051	Unknown table 't1'
Note	1051	Unknown table 't2'
create table t1 ( a varchar(10) not null primary key ) engine=myisam;
create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1);
flush tables;
alter table t1 modify a varchar(10);
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
flush tables;
alter table t1 modify a varchar(10) not null;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`t1`)
drop table if exists t1, t2;
create table t1 (a int, b int, c int, d int, e int, f int, g int, h int,i int, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
insert into t1 (a) values(1);
show table status like 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	MyISAM	10	Fixed	1	37	X	X	X	X	X	X	X	X	latin1_swedish_ci	NULL		
alter table t1 modify a int;
show table status like 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	MyISAM	10	Fixed	1	37	X	X	X	X	X	X	X	X	latin1_swedish_ci	NULL		
drop table t1;
create table t1 (a int not null, b int not null, c int not null, d int not null, e int not null, f int not null, g int not null, h int not null,i int not null, primary key (a,b,c,d,e,f,g,i,h)) engine=MyISAM;
insert into t1 (a) values(1);
Warnings:
Warning	1364	Field 'b' doesn't have a default value
Warning	1364	Field 'c' doesn't have a default value
Warning	1364	Field 'd' doesn't have a default value
Warning	1364	Field 'e' doesn't have a default value
Warning	1364	Field 'f' doesn't have a default value
Warning	1364	Field 'g' doesn't have a default value
Warning	1364	Field 'h' doesn't have a default value
Warning	1364	Field 'i' doesn't have a default value
show table status like 't1';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
t1	MyISAM	10	Fixed	1	37	X	X	X	X	X	X	X	X	latin1_swedish_ci	NULL		
drop table t1;
set names koi8r;
create table t1 (a char(10) character set koi8r);
insert into t1 values ('耘釉');
select a,hex(a) from t1;
a	hex(a)
耘釉	D4C5D3D4
alter table t1 change a a char(10) character set cp1251;
select a,hex(a) from t1;
a	hex(a)
耘釉	F2E5F1F2
alter table t1 change a a binary(4);
select a,hex(a) from t1;
a	hex(a)
蝈耱	F2E5F1F2
alter table t1 change a a char(10) character set cp1251;
select a,hex(a) from t1;
a	hex(a)
耘釉	F2E5F1F2
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;
a	hex(a)
耘釉	D4C5D3D4
alter table t1 change a a varchar(10) character set cp1251;
select a,hex(a) from t1;
a	hex(a)
耘釉	F2E5F1F2
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;
a	hex(a)
耘釉	D4C5D3D4
alter table t1 change a a text character set cp1251;
select a,hex(a) from t1;
a	hex(a)
耘釉	F2E5F1F2
alter table t1 change a a char(10) character set koi8r;
select a,hex(a) from t1;
a	hex(a)
耘釉	D4C5D3D4
delete from t1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) character set koi8r default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 DEFAULT CHARACTER SET latin1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) character set koi8r default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 CONVERT TO CHARACTER SET latin1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 DEFAULT CHARACTER SET cp1251;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
drop table t1;
create table t1 (myblob longblob,mytext longtext) 
default charset latin1 collate latin1_general_cs;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `myblob` longblob,
  `mytext` longtext collate latin1_general_cs
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
alter table t1 character set latin2;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `myblob` longblob,
  `mytext` longtext character set latin1 collate latin1_general_cs
) ENGINE=MyISAM DEFAULT CHARSET=latin2
drop table t1;
CREATE TABLE t1 (a int PRIMARY KEY, b INT UNIQUE);
ALTER TABLE t1 DROP PRIMARY KEY;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) default NULL,
  UNIQUE KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
ALTER TABLE t1 DROP PRIMARY KEY;
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
DROP TABLE t1;
create table t1 (a int, b int, key(a));
insert into t1 values (1,1), (2,2);
alter table t1 drop key no_such_key;
ERROR 42000: Can't DROP 'no_such_key'; check that column/key exists
alter table t1 drop key a;
drop table t1;
CREATE TABLE T12207(a int) ENGINE=MYISAM;
ALTER TABLE T12207 DISCARD TABLESPACE;
ERROR HY000: Table storage engine for 'T12207' doesn't have this option
DROP TABLE T12207;
create table t1 (a text) character set koi8r;
insert into t1 values (_koi8r'耘釉');
select hex(a) from t1;
hex(a)
D4C5D3D4
alter table t1 convert to character set cp1251;
select hex(a) from t1;
hex(a)
F2E5F1F2
drop table t1;
create table t1 ( a timestamp );
alter table t1 add unique ( a(1) );
ERROR HY000: Incorrect sub part key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique sub keys
drop table t1;
create database mysqltest;
create table t1 (c1 int);
alter table t1 rename mysqltest.t1;
drop table t1;
ERROR 42S02: Unknown table 't1'
alter table mysqltest.t1 rename t1;
drop table t1;
create table t1 (c1 int);
use mysqltest;
drop database mysqltest;
alter table test.t1 rename t1;
ERROR 3D000: No database selected
alter table test.t1 rename test.t1;
use test;
drop table t1;
create table t1 (mycol int(10) not null);
alter table t1 alter column mycol set default 0;
desc t1;
Field	Type	Null	Key	Default	Extra
mycol	int(10)	NO		0	
drop table t1;
create table t1 (t varchar(255) default null, key t (t(80)))
engine=myisam default charset=latin1;
alter table t1 change t t text;
drop table t1;