github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/prepare/prepare_transaction.result (about)

     1  drop table if exists user;
     2  CREATE TABLE user
     3  (
     4  id BIGINT(20) NOT NULL COMMENT '主键ID',
     5  age INT(11) NULL DEFAULT NULL COMMENT '年龄',
     6  PRIMARY KEY (id)
     7  );
     8  INSERT INTO user (id, age) VALUES
     9  (1, 18),
    10  (2, 20),
    11  (3, 28),
    12  (4, 21),
    13  (5, 24);
    14  set autocommit=0;
    15  set @id=6;
    16  set @age=6;
    17  prepare s1 from 'insert into user values (?,?)';
    18  execute s1 using @id,@age;
    19  deallocate prepare s1;
    20  commit;
    21  set autocommit=1;
    22  select * from user;
    23  id    age
    24  1    18
    25  2    20
    26  3    28
    27  4    21
    28  5    24
    29  6    6
    30  set autocommit=0;
    31  set @id=6;
    32  prepare s2 from 'delete from user where id=?';
    33  execute s2 using @id;
    34  deallocate prepare s2;
    35  commit;
    36  set autocommit=1;
    37  select * from user;
    38  id    age
    39  1    18
    40  2    20
    41  3    28
    42  4    21
    43  5    24
    44  set autocommit=0;
    45  set @id=5;
    46  set @age=100;
    47  prepare s3 from 'update user set age=? where id=?';
    48  execute s3 using @age, @id;
    49  deallocate prepare s3;
    50  commit;
    51  set autocommit=1;
    52  select * from user;
    53  id    age
    54  1    18
    55  2    20
    56  3    28
    57  4    21
    58  5    100
    59  set autocommit=0;
    60  set @id=3;
    61  prepare s4 from 'select * from user where id>?';
    62  execute s4 using @id;
    63  id    age
    64  4    21
    65  5    100
    66  deallocate prepare s4;
    67  prepare s5 from 'select * from user where id<?';
    68  execute s5 using @id;
    69  id    age
    70  1    18
    71  2    20
    72  deallocate prepare s5;
    73  commit;
    74  set autocommit=1;
    75  set autocommit=0;
    76  prepare s6 from 'create table test_user(a int)';
    77  execute s6;
    78  deallocate prepare s6;
    79  commit;
    80  set autocommit=1;
    81  show tables like 'test_user';
    82  tables_in_prepare_transaction
    83  test_user
    84  set autocommit=0;
    85  prepare s7 from 'drop table test_user';
    86  execute s7;
    87  deallocate prepare s7;
    88  commit;
    89  set autocommit=1;
    90  show tables like 'test_user';
    91  tables_in_prepare_transaction
    92  drop table user;
    93  drop table if exists user;
    94  CREATE TABLE user
    95  (
    96  id BIGINT(20) NOT NULL COMMENT '主键ID',
    97  age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    98  PRIMARY KEY (id)
    99  );
   100  INSERT INTO user (id, age) VALUES
   101  (1, 18),
   102  (2, 20),
   103  (3, 28),
   104  (4, 21),
   105  (5, 24);
   106  set @id=6;
   107  set @age=6;
   108  begin;
   109  prepare s1 from 'insert into user values (?,?)';
   110  execute s1 using @id,@age;
   111  deallocate prepare s1;
   112  commit;
   113  select * from user;
   114  id    age
   115  1    18
   116  2    20
   117  3    28
   118  4    21
   119  5    24
   120  6    6
   121  set @id=6;
   122  begin;
   123  prepare s2 from 'delete from user where id=?';
   124  execute s2 using @id;
   125  deallocate prepare s2;
   126  commit;
   127  select * from user;
   128  id    age
   129  1    18
   130  2    20
   131  3    28
   132  4    21
   133  5    24
   134  set @id=5;
   135  set @age=100;
   136  begin;
   137  prepare s3 from 'update user set age=? where id=?';
   138  execute s3 using @age, @id;
   139  deallocate prepare s3;
   140  commit;
   141  select * from user;
   142  id    age
   143  1    18
   144  2    20
   145  3    28
   146  4    21
   147  5    100
   148  set @id=3;
   149  begin;
   150  prepare s4 from 'select * from user where id>?';
   151  execute s4 using @id;
   152  id    age
   153  4    21
   154  5    100
   155  deallocate prepare s4;
   156  prepare s5 from 'select * from user where id<?';
   157  execute s5 using @id;
   158  id    age
   159  1    18
   160  2    20
   161  deallocate prepare s5;
   162  commit;
   163  begin;
   164  prepare s6 from 'create table test_user(a int)';
   165  execute s6;
   166  deallocate prepare s6;
   167  commit;
   168  show tables like 'test_user';
   169  tables_in_prepare_transaction
   170  test_user
   171  begin;
   172  prepare s7 from 'drop table test_user';
   173  execute s7;
   174  deallocate prepare s7;
   175  commit;
   176  show tables like 'test_user';
   177  tables_in_prepare_transaction