github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/show/IndexMetadata.test (about)

     1  -- @suit
     2  -- @case
     3  -- @desc:index meta data table
     4  -- @label:bvt
     5  DROP DATABASE IF EXISTS test;
     6  CREATE DATABASE test;
     7  USE test;
     8  
     9  -- primary key
    10  drop table if exists T1;
    11  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    12  insert into t1 values(1,"Abby", 24);
    13  insert into t1 values(2,"Bob", 25);
    14  insert into t1 values(3,"Carol", 23);
    15  insert into t1 values(4,"Dora", 29);
    16  create unique index Idx on t1(name);
    17  select * from t1;
    18  show index from t1;
    19  drop table t1;
    20  
    21  -- unique index
    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  show index from t2;
    36  drop table t2;
    37  
    38  -- unique key and unique index, alter index
    39  drop table if exists t3;
    40  create table t3(a int, b int, unique key(a) comment 'a');
    41  create unique index x ON t3(a) comment 'x';
    42  create index xx ON t3(a) comment 'xx';
    43  show create table t3;
    44  show index from t3;
    45  alter table t3 drop index xx;
    46  show index from t3;
    47  drop table t3;
    48  
    49  -- unique index: multicolumn
    50  drop table if exists t4;
    51  create table t4 (
    52  col1 bigint primary key,
    53  col2 varchar(25),
    54  col3 float,
    55  col4 varchar(50)
    56  );
    57  create unique index idx on t4(col2,col3);
    58  insert into t4 values(1,"Abby", 24,'zbcvdf');
    59  insert into t4 values(2,"Bob", 25,'zbcvdf');
    60  insert into t4 values(3,"Carol", 23,'zbcvdf');
    61  insert into t4 values(4,"Dora", 29,'zbcvdf');
    62  select * from t4;
    63  show index from t4;
    64  drop table t4;
    65  
    66  -- unique key
    67  drop table if exists t5;
    68  create table t5(a int, b int, unique key(a) comment 'a');
    69  show index from t5;
    70  drop table t5;
    71  
    72  -- secondary index
    73  drop table if exists t6;
    74  create table t6(a int, b int, unique key(a));
    75  create index b on t6(b);
    76  show index from t6;
    77  drop index b on t6;
    78  show index from t6;
    79  drop table t6;
    80  
    81  drop table if exists t7;
    82  create table t7(a int, b int);
    83  create index x ON t7(a) comment 'x';
    84  show index from t7;
    85  drop table t7;
    86  
    87  -- key
    88  drop table if exists t8;
    89  create table t8(a int, b int, key(a) comment 'a');
    90  show index from t8;
    91  drop table t8;
    92  
    93  -- unique index on multicolumns
    94  drop table if exists t9;
    95  create table t9(
    96  col1 int unsigned,
    97  col2 varchar(15),
    98  col3 varchar(10),
    99  col4 int unsigned,
   100  col5 date,
   101  col6 decimal(7,2),
   102  col7 decimal(7,2),
   103  col8 int unsigned,
   104  unique index(col1,col2,col3,col6)
   105  );
   106  INSERT INTO t9 VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
   107  INSERT INTO t9 VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
   108  
   109  create unique index idx_1 on t9(col1,col2,col3,col6);
   110  select * from t9;
   111  show index from t9;
   112  alter table t9 drop index idx_1;
   113  show index from t9;
   114  drop table t9;
   115  
   116  -- secondary index:multicolumns
   117  drop table if exists t10;
   118  create table t10(a int,b binary,c char,d varchar(20));
   119  create index index01 on t10(a,b,C);
   120  show index from t10;
   121  drop table t10;
   122  
   123  -- uppercase and lowercase
   124  drop table if exists t11;
   125  create table t11(col1 int not null, col2 varchar(100), col3 bigint);
   126  create index ABc on t11(COL1);
   127  show index from t11;
   128  alter table t11 drop index ABc;
   129  show index from t11;
   130  drop table t11;
   131  
   132  DROP DATABASE test;