github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/show/IndexMetadata.result (about) 1 DROP DATABASE IF EXISTS test; 2 CREATE DATABASE test; 3 USE test; 4 drop table if exists T1; 5 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 6 insert into t1 values(1,"Abby", 24); 7 insert into t1 values(2,"Bob", 25); 8 insert into t1 values(3,"Carol", 23); 9 insert into t1 values(4,"Dora", 29); 10 create unique index Idx on t1(name); 11 select * from t1; 12 id name age 13 1 Abby 24 14 2 Bob 25 15 3 Carol 23 16 4 Dora 29 17 show index from t1; 18 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 19 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 20 t1 0 idx 1 name A 0 NULL NULL YES YES NULL 21 drop table t1; 22 drop table if exists t2; 23 create table t2 ( 24 col1 bigint primary key, 25 col2 varchar(25), 26 col3 float, 27 col4 varchar(50) 28 ); 29 create unique index Idx on t2(col2) comment 'create varchar index'; 30 insert into t2 values(1,"Abby", 24,'zbcvdf'); 31 insert into t2 values(2,"Bob", 25,'zbcvdf'); 32 insert into t2 values(3,"Carol", 23,'zbcvdf'); 33 insert into t2 values(4,"Dora", 29,'zbcvdf'); 34 select * from t2; 35 col1 col2 col3 col4 36 1 Abby 24.0 zbcvdf 37 2 Bob 25.0 zbcvdf 38 3 Carol 23.0 zbcvdf 39 4 Dora 29.0 zbcvdf 40 show index from t2; 41 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 42 t2 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 43 t2 0 idx 1 col2 A 0 NULL NULL YES create varchar index YES NULL 44 drop table t2; 45 drop table if exists t3; 46 create table t3(a int, b int, unique key(a) comment 'a'); 47 create unique index x ON t3(a) comment 'x'; 48 create index xx ON t3(a) comment 'xx'; 49 show create table t3; 50 Table Create Table 51 t3 CREATE TABLE `t3` (\n`a` INT DEFAULT NULL,\n`b` INT DEFAULT NULL,\nUNIQUE KEY `a` (`a`) COMMENT 'a',\nUNIQUE KEY `x` (`a`) COMMENT 'x',\nKEY `xx` (`a`) COMMENT 'xx'\n) 52 show index from t3; 53 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 54 t3 0 a 1 a A 0 NULL NULL YES a YES NULL 55 t3 0 x 1 a A 0 NULL NULL YES x YES NULL 56 t3 1 xx 1 a A 0 NULL NULL YES xx YES NULL 57 alter table t3 drop index xx; 58 show index from t3; 59 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 60 t3 0 a 1 a A 0 NULL NULL YES a YES NULL 61 t3 0 x 1 a A 0 NULL NULL YES x YES NULL 62 drop table t3; 63 drop table if exists t4; 64 create table t4 ( 65 col1 bigint primary key, 66 col2 varchar(25), 67 col3 float, 68 col4 varchar(50) 69 ); 70 create unique index idx on t4(col2,col3); 71 insert into t4 values(1,"Abby", 24,'zbcvdf'); 72 insert into t4 values(2,"Bob", 25,'zbcvdf'); 73 insert into t4 values(3,"Carol", 23,'zbcvdf'); 74 insert into t4 values(4,"Dora", 29,'zbcvdf'); 75 select * from t4; 76 col1 col2 col3 col4 77 1 Abby 24.0 zbcvdf 78 2 Bob 25.0 zbcvdf 79 3 Carol 23.0 zbcvdf 80 4 Dora 29.0 zbcvdf 81 show index from t4; 82 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 83 t4 0 PRIMARY 1 col1 A 0 NULL NULL YES NULL 84 t4 0 idx 1 col2 A 0 NULL NULL YES YES NULL 85 t4 0 idx 2 col3 A 0 NULL NULL YES YES NULL 86 drop table t4; 87 drop table if exists t5; 88 create table t5(a int, b int, unique key(a) comment 'a'); 89 show index from t5; 90 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 91 t5 0 a 1 a A 0 NULL NULL YES a YES NULL 92 drop table t5; 93 drop table if exists t6; 94 create table t6(a int, b int, unique key(a)); 95 create index b on t6(b); 96 show index from t6; 97 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 98 t6 0 a 1 a A 0 NULL NULL YES YES NULL 99 t6 1 b 1 b A 0 NULL NULL YES YES NULL 100 drop index b on t6; 101 show index from t6; 102 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 103 t6 0 a 1 a A 0 NULL NULL YES YES NULL 104 drop table t6; 105 drop table if exists t7; 106 create table t7(a int, b int); 107 create index x ON t7(a) comment 'x'; 108 show index from t7; 109 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 110 t7 1 x 1 a A 0 NULL NULL YES x YES NULL 111 drop table t7; 112 drop table if exists t8; 113 create table t8(a int, b int, key(a) comment 'a'); 114 show index from t8; 115 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 116 t8 1 a 1 a A 0 NULL NULL YES a YES NULL 117 drop table t8; 118 drop table if exists t9; 119 create table t9( 120 col1 int unsigned, 121 col2 varchar(15), 122 col3 varchar(10), 123 col4 int unsigned, 124 col5 date, 125 col6 decimal(7,2), 126 col7 decimal(7,2), 127 col8 int unsigned, 128 unique index(col1,col2,col3,col6) 129 ); 130 INSERT INTO t9 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); 131 INSERT INTO t9 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); 132 create unique index idx_1 on t9(col1,col2,col3,col6); 133 select * from t9; 134 col1 col2 col3 col4 col5 col6 col7 col8 135 7369 SMITH CLERK 7902 1980-12-17 800.00 null 20 136 7934 MILLER CLERK 7782 1982-01-23 1300.00 null 10 137 show index from t9; 138 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 139 t9 0 col1 1 col1 A 0 NULL NULL YES YES NULL 140 t9 0 col1 2 col2 A 0 NULL NULL YES YES NULL 141 t9 0 col1 3 col3 A 0 NULL NULL YES YES NULL 142 t9 0 col1 4 col6 A 0 NULL NULL YES YES NULL 143 t9 0 idx_1 1 col1 A 0 NULL NULL YES YES NULL 144 t9 0 idx_1 2 col2 A 0 NULL NULL YES YES NULL 145 t9 0 idx_1 3 col3 A 0 NULL NULL YES YES NULL 146 t9 0 idx_1 4 col6 A 0 NULL NULL YES YES NULL 147 alter table t9 drop index idx_1; 148 show index from t9; 149 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 150 t9 0 col1 1 col1 A 0 NULL NULL YES YES NULL 151 t9 0 col1 2 col2 A 0 NULL NULL YES YES NULL 152 t9 0 col1 3 col3 A 0 NULL NULL YES YES NULL 153 t9 0 col1 4 col6 A 0 NULL NULL YES YES NULL 154 drop table t9; 155 drop table if exists t10; 156 create table t10(a int,b binary,c char,d varchar(20)); 157 create index index01 on t10(a,b,C); 158 show index from t10; 159 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 160 t10 1 index01 1 a A 0 NULL NULL YES YES NULL 161 t10 1 index01 2 b A 0 NULL NULL YES YES NULL 162 t10 1 index01 3 c A 0 NULL NULL YES YES NULL 163 drop table t10; 164 drop table if exists t11; 165 create table t11(col1 int not null, col2 varchar(100), col3 bigint); 166 create index ABc on t11(COL1); 167 show index from t11; 168 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 169 t11 1 abc 1 col1 A 0 NULL NULL YES NULL 170 alter table t11 drop index ABc; 171 show index from t11; 172 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 173 drop table t11; 174 DROP DATABASE test;