| 1 |
create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb default charset=ucs2; |
| 2 |
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); |
| 3 |
commit; |
| 4 |
alter table t1 add unique index (b); |
| 5 |
ERROR 23000: Duplicate entry '2' for key 'b' |
| 6 |
insert into t1 values(8,9,'fff','fff'); |
| 7 |
select * from t1; |
| 8 |
a b c d |
| 9 |
1 1 ab ab |
| 10 |
2 2 ac ac |
| 11 |
3 2 ad ad |
| 12 |
4 4 afe afe |
| 13 |
8 9 fff fff |
| 14 |
show create table t1; |
| 15 |
Table Create Table |
| 16 |
t1 CREATE TABLE `t1` ( |
| 17 |
`a` int(11) NOT NULL, |
| 18 |
`b` int(11) DEFAULT NULL, |
| 19 |
`c` char(10) DEFAULT NULL, |
| 20 |
`d` varchar(20) DEFAULT NULL, |
| 21 |
PRIMARY KEY (`a`) |
| 22 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci |
| 23 |
alter table t1 add index (b); |
| 24 |
insert into t1 values(10,10,'kkk','iii'); |
| 25 |
select * from t1; |
| 26 |
a b c d |
| 27 |
1 1 ab ab |
| 28 |
2 2 ac ac |
| 29 |
3 2 ad ad |
| 30 |
4 4 afe afe |
| 31 |
8 9 fff fff |
| 32 |
10 10 kkk iii |
| 33 |
select * from t1 force index(b) order by b; |
| 34 |
a b c d |
| 35 |
1 1 ab ab |
| 36 |
2 2 ac ac |
| 37 |
3 2 ad ad |
| 38 |
4 4 afe afe |
| 39 |
8 9 fff fff |
| 40 |
10 10 kkk iii |
| 41 |
explain select * from t1 force index(b) order by b; |
| 42 |
id select_type table type possible_keys key key_len ref rows Extra |
| 43 |
1 SIMPLE t1 index NULL b 5 NULL 6 |
| 44 |
show create table t1; |
| 45 |
Table Create Table |
| 46 |
t1 CREATE TABLE `t1` ( |
| 47 |
`a` int(11) NOT NULL, |
| 48 |
`b` int(11) DEFAULT NULL, |
| 49 |
`c` char(10) DEFAULT NULL, |
| 50 |
`d` varchar(20) DEFAULT NULL, |
| 51 |
PRIMARY KEY (`a`), |
| 52 |
KEY `b` (`b`) |
| 53 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci |
| 54 |
alter table t1 add unique index (c), add index (d); |
| 55 |
insert into t1 values(11,11,'aaa','mmm'); |
| 56 |
select * from t1; |
| 57 |
a b c d |
| 58 |
1 1 ab ab |
| 59 |
2 2 ac ac |
| 60 |
3 2 ad ad |
| 61 |
4 4 afe afe |
| 62 |
8 9 fff fff |
| 63 |
10 10 kkk iii |
| 64 |
11 11 aaa mmm |
| 65 |
select * from t1 force index(b) order by b; |
| 66 |
a b c d |
| 67 |
1 1 ab ab |
| 68 |
2 2 ac ac |
| 69 |
3 2 ad ad |
| 70 |
4 4 afe afe |
| 71 |
8 9 fff fff |
| 72 |
10 10 kkk iii |
| 73 |
11 11 aaa mmm |
| 74 |
select * from t1 force index(c) order by c; |
| 75 |
a b c d |
| 76 |
11 11 aaa mmm |
| 77 |
1 1 ab ab |
| 78 |
2 2 ac ac |
| 79 |
3 2 ad ad |
| 80 |
4 4 afe afe |
| 81 |
8 9 fff fff |
| 82 |
10 10 kkk iii |
| 83 |
select * from t1 force index(d) order by d; |
| 84 |
a b c d |
| 85 |
1 1 ab ab |
| 86 |
2 2 ac ac |
| 87 |
3 2 ad ad |
| 88 |
4 4 afe afe |
| 89 |
8 9 fff fff |
| 90 |
10 10 kkk iii |
| 91 |
11 11 aaa mmm |
| 92 |
explain select * from t1 force index(b) order by b; |
| 93 |
id select_type table type possible_keys key key_len ref rows Extra |
| 94 |
1 SIMPLE t1 index NULL b 5 NULL 7 |
| 95 |
explain select * from t1 force index(c) order by c; |
| 96 |
id select_type table type possible_keys key key_len ref rows Extra |
| 97 |
1 SIMPLE t1 index NULL c 21 NULL 7 |
| 98 |
explain select * from t1 force index(d) order by d; |
| 99 |
id select_type table type possible_keys key key_len ref rows Extra |
| 100 |
1 SIMPLE t1 index NULL d 43 NULL 7 |
| 101 |
show create table t1; |
| 102 |
Table Create Table |
| 103 |
t1 CREATE TABLE `t1` ( |
| 104 |
`a` int(11) NOT NULL, |
| 105 |
`b` int(11) DEFAULT NULL, |
| 106 |
`c` char(10) DEFAULT NULL, |
| 107 |
`d` varchar(20) DEFAULT NULL, |
| 108 |
PRIMARY KEY (`a`), |
| 109 |
UNIQUE KEY `c` (`c`), |
| 110 |
KEY `b` (`b`), |
| 111 |
KEY `d` (`d`) |
| 112 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_uca1400_ai_ci |
| 113 |
check table t1; |
| 114 |
Table Op Msg_type Msg_text |
| 115 |
test.t1 check status OK |
| 116 |
drop table t1; |