| 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 |
Warnings: |
| 3 |
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead |
| 4 |
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe'); |
| 5 |
commit; |
| 6 |
alter table t1 add unique index (b); |
| 7 |
ERROR 23000: Duplicate entry '2' for key 't1.b' |
| 8 |
insert into t1 values(8,9,'fff','fff'); |
| 9 |
select * from t1; |
| 10 |
a b c d |
| 11 |
1 1 ab ab |
| 12 |
2 2 ac ac |
| 13 |
3 2 ad ad |
| 14 |
4 4 afe afe |
| 15 |
8 9 fff fff |
| 16 |
show create table t1; |
| 17 |
Table Create Table |
| 18 |
t1 CREATE TABLE `t1` ( |
| 19 |
`a` int NOT NULL, |
| 20 |
`b` int DEFAULT NULL, |
| 21 |
`c` char(10) DEFAULT NULL, |
| 22 |
`d` varchar(20) DEFAULT NULL, |
| 23 |
PRIMARY KEY (`a`) |
| 24 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 |
| 25 |
alter table t1 add index (b); |
| 26 |
insert into t1 values(10,10,'kkk','iii'); |
| 27 |
select * from t1; |
| 28 |
a b c d |
| 29 |
1 1 ab ab |
| 30 |
2 2 ac ac |
| 31 |
3 2 ad ad |
| 32 |
4 4 afe afe |
| 33 |
8 9 fff fff |
| 34 |
10 10 kkk iii |
| 35 |
select * from t1 force index(b) order by b; |
| 36 |
a b c d |
| 37 |
1 1 ab ab |
| 38 |
2 2 ac ac |
| 39 |
3 2 ad ad |
| 40 |
4 4 afe afe |
| 41 |
8 9 fff fff |
| 42 |
10 10 kkk iii |
| 43 |
explain select * from t1 force index(b) order by b; |
| 44 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 45 |
1 SIMPLE t1 NULL index NULL b 5 NULL 6 100.00 NULL |
| 46 |
Warnings: |
| 47 |
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` |
| 48 |
show create table t1; |
| 49 |
Table Create Table |
| 50 |
t1 CREATE TABLE `t1` ( |
| 51 |
`a` int NOT NULL, |
| 52 |
`b` int DEFAULT NULL, |
| 53 |
`c` char(10) DEFAULT NULL, |
| 54 |
`d` varchar(20) DEFAULT NULL, |
| 55 |
PRIMARY KEY (`a`), |
| 56 |
KEY `b` (`b`) |
| 57 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 |
| 58 |
alter table t1 add unique index (c), add index (d); |
| 59 |
insert into t1 values(11,11,'aaa','mmm'); |
| 60 |
select * from t1; |
| 61 |
a b c d |
| 62 |
1 1 ab ab |
| 63 |
2 2 ac ac |
| 64 |
3 2 ad ad |
| 65 |
4 4 afe afe |
| 66 |
8 9 fff fff |
| 67 |
10 10 kkk iii |
| 68 |
11 11 aaa mmm |
| 69 |
select * from t1 force index(b) order by b; |
| 70 |
a b c d |
| 71 |
1 1 ab ab |
| 72 |
2 2 ac ac |
| 73 |
3 2 ad ad |
| 74 |
4 4 afe afe |
| 75 |
8 9 fff fff |
| 76 |
10 10 kkk iii |
| 77 |
11 11 aaa mmm |
| 78 |
select * from t1 force index(c) order by c; |
| 79 |
a b c d |
| 80 |
11 11 aaa mmm |
| 81 |
1 1 ab ab |
| 82 |
2 2 ac ac |
| 83 |
3 2 ad ad |
| 84 |
4 4 afe afe |
| 85 |
8 9 fff fff |
| 86 |
10 10 kkk iii |
| 87 |
select * from t1 force index(d) order by d; |
| 88 |
a b c d |
| 89 |
1 1 ab ab |
| 90 |
2 2 ac ac |
| 91 |
3 2 ad ad |
| 92 |
4 4 afe afe |
| 93 |
8 9 fff fff |
| 94 |
10 10 kkk iii |
| 95 |
11 11 aaa mmm |
| 96 |
explain select * from t1 force index(b) order by b; |
| 97 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 98 |
1 SIMPLE t1 NULL index NULL b 5 NULL 7 100.00 NULL |
| 99 |
Warnings: |
| 100 |
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`b`) order by `test`.`t1`.`b` |
| 101 |
explain select * from t1 force index(c) order by c; |
| 102 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 103 |
1 SIMPLE t1 NULL index NULL c 21 NULL 7 100.00 NULL |
| 104 |
Warnings: |
| 105 |
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`c`) order by `test`.`t1`.`c` |
| 106 |
explain select * from t1 force index(d) order by d; |
| 107 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 108 |
1 SIMPLE t1 NULL index NULL d 43 NULL 7 100.00 NULL |
| 109 |
Warnings: |
| 110 |
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` FORCE INDEX (`d`) order by `test`.`t1`.`d` |
| 111 |
show create table t1; |
| 112 |
Table Create Table |
| 113 |
t1 CREATE TABLE `t1` ( |
| 114 |
`a` int NOT NULL, |
| 115 |
`b` int DEFAULT NULL, |
| 116 |
`c` char(10) DEFAULT NULL, |
| 117 |
`d` varchar(20) DEFAULT NULL, |
| 118 |
PRIMARY KEY (`a`), |
| 119 |
UNIQUE KEY `c` (`c`), |
| 120 |
KEY `b` (`b`), |
| 121 |
KEY `d` (`d`) |
| 122 |
) ENGINE=InnoDB DEFAULT CHARSET=ucs2 |
| 123 |
check table t1; |
| 124 |
Table Op Msg_type Msg_text |
| 125 |
test.t1 check status OK |
| 126 |
drop table t1; |