767 lines
31 KiB
Plaintext
767 lines
31 KiB
Plaintext
CREATE TABLE t1 (
|
|
a INT NOT NULL,
|
|
b INT NOT NULL
|
|
) ENGINE=ndbcluster;
|
|
INSERT INTO t1 VALUES (9410,9412);
|
|
ALTER TABLE t1 ADD COLUMN c int not null;
|
|
SELECT * FROM t1;
|
|
a b c
|
|
9410 9412 0
|
|
DROP TABLE t1;
|
|
CREATE DATABASE mysqltest;
|
|
USE mysqltest;
|
|
CREATE TABLE t1 (
|
|
a INT NOT NULL,
|
|
b INT NOT NULL
|
|
) ENGINE=ndbcluster;
|
|
RENAME TABLE t1 TO test.t1;
|
|
SHOW TABLES;
|
|
Tables_in_mysqltest
|
|
DROP DATABASE mysqltest;
|
|
USE test;
|
|
SHOW TABLES;
|
|
Tables_in_test
|
|
t1
|
|
DROP TABLE t1;
|
|
create table t1 (
|
|
col1 int not null auto_increment primary key,
|
|
col2 varchar(30) not null,
|
|
col3 varchar (20) not null,
|
|
col4 varchar(4) not null,
|
|
col5 enum('PENDING', 'ACTIVE', 'DISABLED') not null,
|
|
col6 int not null, to_be_deleted int) ENGINE=ndbcluster charset=latin1;
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL #
|
|
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
|
|
insert into t1 values
|
|
(0,4,3,5,"PENDING",1,7),(NULL,4,3,5,"PENDING",1,7),(31,4,3,5,"PENDING",1,7), (7,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7), (100,4,3,5,"PENDING",1,7), (99,4,3,5,"PENDING",1,7), (8,4,3,5,"PENDING",1,7), (NULL,4,3,5,"PENDING",1,7);
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL #
|
|
select * from t1 order by col1;
|
|
col1 col2 col3 col4 col5 col6 to_be_deleted
|
|
0 4 3 5 PENDING 1 7
|
|
1 4 3 5 PENDING 1 7
|
|
7 4 3 5 PENDING 1 7
|
|
8 4 3 5 PENDING 1 7
|
|
31 4 3 5 PENDING 1 7
|
|
32 4 3 5 PENDING 1 7
|
|
99 4 3 5 PENDING 1 7
|
|
100 4 3 5 PENDING 1 7
|
|
101 4 3 5 PENDING 1 7
|
|
alter table t1
|
|
add column col4_5 varchar(20) not null after col4,
|
|
add column col7 varchar(30) not null after col5,
|
|
add column col8 datetime not null, drop column to_be_deleted,
|
|
change column col2 fourth varchar(30) not null after col3,
|
|
modify column col6 int not null first;
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL #
|
|
select * from t1 order by col1;
|
|
col6 col1 col3 fourth col4 col4_5 col5 col7 col8
|
|
1 0 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 1 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 7 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 8 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 31 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 32 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 99 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 100 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 101 3 4 5 PENDING 0000-00-00 00:00:00
|
|
insert into t1 values (2, NULL,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
|
|
show table status;
|
|
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
|
|
t1 ndbcluster 10 Dynamic 0 # # # 0 # 1 # # # latin1_swedish_ci NULL #
|
|
select * from t1 order by col1;
|
|
col6 col1 col3 fourth col4 col4_5 col5 col7 col8
|
|
1 0 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 1 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 7 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 8 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 31 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 32 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 99 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 100 3 4 5 PENDING 0000-00-00 00:00:00
|
|
1 101 3 4 5 PENDING 0000-00-00 00:00:00
|
|
2 102 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00
|
|
delete from t1;
|
|
insert into t1 values (0,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
|
|
SET SQL_MODE='';
|
|
insert into t1 values (1,0,4,3,5,99,"PENDING","EXTRA",'2004-01-01 00:00:00');
|
|
select * from t1 order by col1;
|
|
col6 col1 col3 fourth col4 col4_5 col5 col7 col8
|
|
0 0 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00
|
|
1 103 4 3 5 99 PENDING EXTRA 2004-01-01 00:00:00
|
|
alter table t1 drop column col4_5;
|
|
insert into t1 values (2,0,4,3,5,"PENDING","EXTRA",'2004-01-01 00:00:00');
|
|
select * from t1 order by col1;
|
|
col6 col1 col3 fourth col4 col5 col7 col8
|
|
0 0 4 3 5 PENDING EXTRA 2004-01-01 00:00:00
|
|
1 103 4 3 5 PENDING EXTRA 2004-01-01 00:00:00
|
|
2 104 4 3 5 PENDING EXTRA 2004-01-01 00:00:00
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a INT NOT NULL,
|
|
b INT NOT NULL
|
|
) ENGINE=ndbcluster;
|
|
INSERT INTO t1 VALUES (9410,9412);
|
|
ALTER TABLE t1 ADD COLUMN c int not null;
|
|
select * from t1 order by a;
|
|
a b c
|
|
9410 9412 0
|
|
select * from t1 order by a;
|
|
a b c
|
|
9410 9412 0
|
|
alter table t1 drop c;
|
|
select * from t1 order by a;
|
|
a b
|
|
9410 9412
|
|
drop table t1;
|
|
select * from t1 order by a;
|
|
ERROR 42S02: Table 'test.t1' doesn't exist
|
|
CREATE TABLE t1 (
|
|
a INT NOT NULL PRIMARY KEY,
|
|
b INT NOT NULL
|
|
) ENGINE=ndbcluster;
|
|
INSERT INTO t1 VALUES (0,1),(17,18);
|
|
select * from t1 order by a;
|
|
a b
|
|
0 1
|
|
17 18
|
|
SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
|
|
alter table t1 modify column a int not null auto_increment;
|
|
SET SQL_MODE='';
|
|
select * from t1 order by a;
|
|
a b
|
|
0 1
|
|
17 18
|
|
INSERT INTO t1 VALUES (0,19),(20,21);
|
|
select * from t1 order by a;
|
|
a b
|
|
0 1
|
|
17 18
|
|
18 19
|
|
20 21
|
|
drop table t1;
|
|
CREATE TABLE t1 (
|
|
a INT NOT NULL PRIMARY KEY,
|
|
b INT NOT NULL
|
|
) ENGINE=ndbcluster;
|
|
INSERT INTO t1 VALUES (0,1),(17,18);
|
|
select * from t1 order by a;
|
|
a b
|
|
0 1
|
|
17 18
|
|
alter table t1 add c int not null unique auto_increment;
|
|
select c from t1 order by c;
|
|
c
|
|
1
|
|
2
|
|
INSERT INTO t1 VALUES (1,2,0),(18,19,4),(20,21,0);
|
|
select c from t1 order by c;
|
|
c
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
drop table t1;
|
|
create table t1 (
|
|
ai bigint auto_increment,
|
|
c001 int(11) not null,
|
|
c002 int(11) not null,
|
|
c003 int(11) not null,
|
|
c004 int(11) not null,
|
|
c005 int(11) not null,
|
|
c006 int(11) not null,
|
|
c007 int(11) not null,
|
|
c008 int(11) not null,
|
|
c009 int(11) not null,
|
|
c010 int(11) not null,
|
|
c011 int(11) not null,
|
|
c012 int(11) not null,
|
|
c013 int(11) not null,
|
|
c014 int(11) not null,
|
|
c015 int(11) not null,
|
|
c016 int(11) not null,
|
|
c017 int(11) not null,
|
|
c018 int(11) not null,
|
|
c019 int(11) not null,
|
|
c020 int(11) not null,
|
|
c021 int(11) not null,
|
|
c022 int(11) not null,
|
|
c023 int(11) not null,
|
|
c024 int(11) not null,
|
|
c025 int(11) not null,
|
|
c026 int(11) not null,
|
|
c027 int(11) not null,
|
|
c028 int(11) not null,
|
|
c029 int(11) not null,
|
|
c030 int(11) not null,
|
|
c031 int(11) not null,
|
|
c032 int(11) not null,
|
|
c033 int(11) not null,
|
|
c034 int(11) not null,
|
|
c035 int(11) not null,
|
|
c036 int(11) not null,
|
|
c037 int(11) not null,
|
|
c038 int(11) not null,
|
|
c039 int(11) not null,
|
|
c040 int(11) not null,
|
|
c041 int(11) not null,
|
|
c042 int(11) not null,
|
|
c043 int(11) not null,
|
|
c044 int(11) not null,
|
|
c045 int(11) not null,
|
|
c046 int(11) not null,
|
|
c047 int(11) not null,
|
|
c048 int(11) not null,
|
|
c049 int(11) not null,
|
|
c050 int(11) not null,
|
|
c051 int(11) not null,
|
|
c052 int(11) not null,
|
|
c053 int(11) not null,
|
|
c054 int(11) not null,
|
|
c055 int(11) not null,
|
|
c056 int(11) not null,
|
|
c057 int(11) not null,
|
|
c058 int(11) not null,
|
|
c059 int(11) not null,
|
|
c060 int(11) not null,
|
|
c061 int(11) not null,
|
|
c062 int(11) not null,
|
|
c063 int(11) not null,
|
|
c064 int(11) not null,
|
|
c065 int(11) not null,
|
|
c066 int(11) not null,
|
|
c067 int(11) not null,
|
|
c068 int(11) not null,
|
|
c069 int(11) not null,
|
|
c070 int(11) not null,
|
|
c071 int(11) not null,
|
|
c072 int(11) not null,
|
|
c073 int(11) not null,
|
|
c074 int(11) not null,
|
|
c075 int(11) not null,
|
|
c076 int(11) not null,
|
|
c077 int(11) not null,
|
|
c078 int(11) not null,
|
|
c079 int(11) not null,
|
|
c080 int(11) not null,
|
|
c081 int(11) not null,
|
|
c082 int(11) not null,
|
|
c083 int(11) not null,
|
|
c084 int(11) not null,
|
|
c085 int(11) not null,
|
|
c086 int(11) not null,
|
|
c087 int(11) not null,
|
|
c088 int(11) not null,
|
|
c089 int(11) not null,
|
|
c090 int(11) not null,
|
|
c091 int(11) not null,
|
|
c092 int(11) not null,
|
|
c093 int(11) not null,
|
|
c094 int(11) not null,
|
|
c095 int(11) not null,
|
|
c096 int(11) not null,
|
|
c097 int(11) not null,
|
|
c098 int(11) not null,
|
|
c099 int(11) not null,
|
|
c100 int(11) not null,
|
|
c101 int(11) not null,
|
|
c102 int(11) not null,
|
|
c103 int(11) not null,
|
|
c104 int(11) not null,
|
|
c105 int(11) not null,
|
|
c106 int(11) not null,
|
|
c107 int(11) not null,
|
|
c108 int(11) not null,
|
|
c109 int(11) not null,
|
|
primary key (ai),
|
|
unique key tx1 (c002, c003, c004, c005)) engine=ndb;
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
create index tx2
|
|
on t1 (c010, c011, c012, c013);
|
|
drop table t1;
|
|
create table t1 (a int primary key auto_increment, b int) engine=ndb;
|
|
insert into t1 (b) values (101),(102),(103);
|
|
select * from t1 where a = 3;
|
|
a b
|
|
3 103
|
|
alter table t1 rename t2;
|
|
insert into t2 (b) values (201),(202),(203);
|
|
select * from t2 where a = 6;
|
|
a b
|
|
6 203
|
|
alter table t2 add c int;
|
|
Warnings:
|
|
Warning 1478 Converted FIXED field 'c' to DYNAMIC to enable online ADD COLUMN
|
|
insert into t2 (b) values (301),(302),(303);
|
|
select * from t2 where a = 9;
|
|
a b c
|
|
9 303 NULL
|
|
alter table t2 rename t1;
|
|
insert into t1 (b) values (401),(402),(403);
|
|
select * from t1 where a = 12;
|
|
a b c
|
|
12 403 NULL
|
|
drop table t1;
|
|
create table t1(a int not null) engine=ndb;
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
insert into t1 values (1),(2),(3);
|
|
alter table t1 add primary key (a);
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
|
|
PRIMARY KEY(a) - UniqueHashIndex
|
|
PRIMARY(a) - OrderedIndex
|
|
update t1 set a = 17 where a = 1;
|
|
select * from t1 order by a;
|
|
a
|
|
2
|
|
3
|
|
17
|
|
alter table t1 drop primary key;
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
update t1 set a = 1 where a = 17;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
drop table t1;
|
|
create table t1(a int not null) engine=ndb;
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
insert into t1 values (1),(2),(3);
|
|
create unique index pk on t1(a);
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
|
|
PRIMARY KEY(a) - UniqueHashIndex
|
|
update t1 set a = 17 where a = 1;
|
|
select * from t1 order by a;
|
|
a
|
|
2
|
|
3
|
|
17
|
|
alter table t1 drop index pk;
|
|
Primary keys:
|
|
Number of primary keys: 1
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
update t1 set a = 1 where a = 17;
|
|
select * from t1 order by a;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
drop table t1;
|
|
create table t1 (a int not null primary key, b int not null default 0, c varchar(254)) engine=ndb;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL DEFAULT '0',
|
|
`c` varchar(254) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
alter table t1 alter b set default 1;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL DEFAULT '1',
|
|
`c` varchar(254) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
drop table t1;
|
|
create table t1 (a int not null, b int not null) engine=ndb;
|
|
insert into t1 values (1, 300), (2, 200), (3, 100);
|
|
select * from t1 order by a;
|
|
a b
|
|
1 300
|
|
2 200
|
|
3 100
|
|
alter table t1 order by b;
|
|
select * from t1 order by b;
|
|
a b
|
|
3 100
|
|
2 200
|
|
1 300
|
|
drop table t1;
|
|
create table t1 (
|
|
ci001 int, ci002 int, ci003 int, ci004 int, ci005 int, ci006 int, ci007 int, ci008 int,
|
|
ci009 int, ci010 int, ci011 int, ci012 int, ci013 int, ci014 int, ci015 int, ci016 int,
|
|
ci017 int, ci018 int, ci019 int, ci020 int, ci021 int, ci022 int, ci023 int, ci024 int,
|
|
ci025 int, ci026 int, ci027 int, ci028 int, ci029 int, ci030 int, ci031 int, ci032 int,
|
|
ci033 int, ci034 int, ci035 int, ci036 int, ci037 int, ci038 int, ci039 int, ci040 int,
|
|
ci041 int, ci042 int, ci043 int, ci044 int, ci045 int, ci046 int, ci047 int, ci048 int,
|
|
ci049 int, ci050 int, ci051 int, ci052 int, ci053 int, ci054 int, ci055 int, ci056 int,
|
|
ci057 int, ci058 int, ci059 int, ci060 int, ci061 int, ci062 int, ci063 int, ci064 int,
|
|
ci065 int, ci066 int, ci067 int, ci068 int, ci069 int, ci070 int, ci071 int, ci072 int,
|
|
ci073 int, ci074 int, ci075 int, ci076 int, ci077 int, ci078 int, ci079 int, ci080 int,
|
|
ci081 int, ci082 int, ci083 int, ci084 int, ci085 int, ci086 int, ci087 int, ci088 int,
|
|
ci089 int, ci090 int, ci091 int, ci092 int, ci093 int, ci094 int, ci095 int, ci096 int,
|
|
ci097 int, ci098 int, ci099 int, ci100 int, ci101 int, ci102 int, ci103 int, ci104 int,
|
|
ci105 int, ci106 int, ci107 int, ci108 int, ci109 int, ci110 int, ci111 int, ci112 int,
|
|
ci113 int, ci114 int, ci115 int, ci116 int, ci117 int, ci118 int, ci119 int, ci120 int,
|
|
ci121 int, ci122 int, ci123 int, ci124 int, ci125 int, ci126 int, ci127 int, ci128 int,
|
|
ci129 int, ci130 int, ci131 int, ci132 int, ci133 int, ci134 int, ci135 int, ci136 int,
|
|
ci137 int, ci138 int, ci139 int, ci140 int, ci141 int, ci142 int, ci143 int, ci144 int,
|
|
ci145 int, ci146 int, ci147 int, ci148 int, ci149 int, ci150 int, ci151 int, ci152 int,
|
|
ci153 int, ci154 int, ci155 int, ci156 int, ci157 int, ci158 int, ci159 int, ci160 int,
|
|
ci161 int, ci162 int, ci163 int, ci164 int, ci165 int, ci166 int, ci167 int, ci168 int,
|
|
ci169 int, ci170 int, ci171 int, ci172 int, ci173 int, ci174 int, ci175 int, ci176 int,
|
|
ci177 int, ci178 int, ci179 int, ci180 int, ci181 int, ci182 int, ci183 int, ci184 int,
|
|
ci185 int, ci186 int, ci187 int, ci188 int, ci189 int, ci190 int, ci191 int, ci192 int,
|
|
ci193 int, ci194 int, ci195 int, ci196 int, ci197 int, ci198 int, ci199 int, ci200 int,
|
|
ci201 int, ci202 int, ci203 int, ci204 int, ci205 int, ci206 int, ci207 int, ci208 int,
|
|
ci209 int, ci210 int, ci211 int, ci212 int, ci213 int, ci214 int, ci215 int, ci216 int,
|
|
ci217 int, ci218 int, ci219 int, ci220 int, ci221 int, ci222 int, ci223 int, ci224 int,
|
|
ci225 int, ci226 int, ci227 int, ci228 int, ci229 int, ci230 int, ci231 int, ci232 int,
|
|
ci233 int, ci234 int, ci235 int, ci236 int, ci237 int, ci238 int, ci239 int, ci240 int,
|
|
ci241 int, ci242 int, ci243 int, ci244 int, ci245 int, ci246 int, ci247 int, ci248 int,
|
|
ci249 int, ci250 int, ci251 int, ci252 int, ci253 int, ci254 int, ci255 int, ci256 int,
|
|
ci257 int, ci258 int, ci259 int, ci260 int, ci261 int, ci262 int, ci263 int, ci264 int,
|
|
ci265 int, ci266 int, ci267 int, ci268 int, ci269 int, ci270 int, ci271 int, ci272 int,
|
|
ci273 int, ci274 int, ci275 int, ci276 int, ci277 int, ci278 int, ci279 int, ci280 int,
|
|
ci281 int, ci282 int, ci283 int, ci284 int, ci285 int, ci286 int, ci287 int, ci288 int,
|
|
ci289 int, ci290 int, ci291 int, ci292 int, ci293 int, ci294 int, ci295 int, ci296 int,
|
|
ci297 int, ci298 int, ci299 int, ci300 int, ci301 int, ci302 int, ci303 int, ci304 int,
|
|
ci305 int, ci306 int, ci307 int, ci308 int, ci309 int, ci310 int, ci311 int, ci312 int,
|
|
ci313 int, ci314 int, ci315 int, ci316 int, ci317 int, ci318 int, ci319 int, ci320 int,
|
|
ci321 int, ci322 int, ci323 int, ci324 int, ci325 int, ci326 int, ci327 int, ci328 int,
|
|
ci329 int, ci330 int, ci331 int, ci332 int, ci333 int, ci334 int, ci335 int, ci336 int,
|
|
ci337 int, ci338 int, ci339 int, ci340 int, ci341 int, ci342 int, ci343 int, ci344 int,
|
|
ci345 int, ci346 int, ci347 int, ci348 int, ci349 int, ci350 int, ci351 int, ci352 int,
|
|
ci353 int, ci354 int, ci355 int, ci356 int, ci357 int, ci358 int, ci359 int, ci360 int,
|
|
ci361 int, ci362 int, ci363 int, ci364 int, ci365 int, ci366 int, ci367 int, ci368 int,
|
|
ci369 int, ci370 int, ci371 int, ci372 int, ci373 int, ci374 int, ci375 int, ci376 int,
|
|
ci377 int, ci378 int, ci379 int, ci380 int, ci381 int, ci382 int, ci383 int, ci384 int,
|
|
ci385 int, ci386 int, ci387 int, ci388 int, ci389 int, ci390 int, ci391 int, ci392 int,
|
|
ci393 int, ci394 int, ci395 int, ci396 int, ci397 int, ci398 int, ci399 int, ci400 int,
|
|
ci401 int, ci402 int, ci403 int, ci404 int, ci405 int, ci406 int, ci407 int, ci408 int,
|
|
ci409 int, ci410 int, ci411 int, ci412 int, ci413 int, ci414 int, ci415 int, ci416 int,
|
|
ci417 int, ci418 int, ci419 int, ci420 int, ci421 int, ci422 int, ci423 int, ci424 int,
|
|
ci425 int, ci426 int, ci427 int, ci428 int, ci429 int, ci430 int, ci431 int, ci432 int,
|
|
ci433 int, ci434 int, ci435 int, ci436 int, ci437 int, ci438 int, ci439 int, ci440 int,
|
|
ci441 int, ci442 int, ci443 int, ci444 int, ci445 int, ci446 int, ci447 int, ci448 int,
|
|
ci449 int, ci450 int, ci451 int, ci452 int, ci453 int, ci454 int, ci455 int, ci456 int,
|
|
ci457 int, ci458 int, ci459 int, ci460 int, ci461 int, ci462 int, ci463 int, ci464 int,
|
|
ci465 int, ci466 int, ci467 int, ci468 int, ci469 int, ci470 int,
|
|
cb471 blob, cb472 blob, cb473 blob, cb474 blob, cb475 blob, cb476 blob, cb477 blob, cb478 blob,
|
|
cb479 blob, cb480 blob, cb481 blob, cb482 blob, cb483 blob, cb484 blob, cb485 blob, cb486 blob,
|
|
cb487 blob, cb488 blob, cb489 blob, cb490 blob, cb491 blob, cb492 blob, cb493 blob, cb494 blob,
|
|
cb495 blob, cb496 blob, cb497 blob, cb498 blob, cb499 blob, cb500 blob, cb501 blob, cb502 blob,
|
|
cb503 blob, cb504 blob, cb505 blob, cb506 blob, cb507 blob, cb508 blob, cb509 blob, cb510 blob,
|
|
cb511 blob, cb512 blob,
|
|
primary key (ci001),
|
|
unique i1 (ci002),
|
|
unique i2 (ci003),
|
|
unique i3 (ci004),
|
|
unique i4 (ci005),
|
|
unique i5 (ci006),
|
|
unique i6 (ci007),
|
|
unique i7 (ci008),
|
|
unique i8 (ci009),
|
|
unique i9 (ci010),
|
|
unique i10 (ci011),
|
|
unique i11 (ci012),
|
|
unique i12 (ci013),
|
|
unique i13 (ci014),
|
|
unique i14 (ci015),
|
|
unique i15 (ci016),
|
|
unique i16 (ci017),
|
|
unique i17 (ci018),
|
|
unique i18 (ci019),
|
|
unique i19 (ci020),
|
|
unique i20 (ci021),
|
|
unique i21 (ci022),
|
|
unique i22 (ci023),
|
|
unique i23 (ci024),
|
|
unique i24 (ci025),
|
|
unique i25 (ci026),
|
|
unique i26 (ci027),
|
|
unique i27 (ci028),
|
|
unique i28 (ci029),
|
|
unique i29 (ci030),
|
|
unique i30 (ci031),
|
|
unique i31 (ci032),
|
|
unique i32 (ci033),
|
|
unique i33 (ci034),
|
|
unique i34 (ci035),
|
|
unique i35 (ci036),
|
|
unique i36 (ci037),
|
|
unique i37 (ci038),
|
|
unique i38 (ci039),
|
|
unique i39 (ci040),
|
|
unique i40 (ci041),
|
|
unique i41 (ci042),
|
|
unique i42 (ci043),
|
|
unique i43 (ci044),
|
|
unique i44 (ci045),
|
|
unique i45 (ci046),
|
|
unique i46 (ci047),
|
|
unique i47 (ci048),
|
|
unique i48 (ci049),
|
|
unique i49 (ci050),
|
|
unique i50 (ci051),
|
|
unique i51 (ci052),
|
|
unique i52 (ci053),
|
|
unique i53 (ci054),
|
|
unique i54 (ci055),
|
|
unique i55 (ci056),
|
|
unique i56 (ci057),
|
|
unique i57 (ci058),
|
|
unique i58 (ci059),
|
|
unique i59 (ci060),
|
|
unique i60 (ci061),
|
|
unique i61 (ci062),
|
|
unique i62 (ci063),
|
|
unique i63 (ci064)
|
|
) engine=ndb charset=latin1;
|
|
drop table t1;
|
|
CREATE LOGFILE GROUP lg1
|
|
ADD UNDOFILE 'undofile.dat'
|
|
ENGINE = ndb;
|
|
CREATE TABLESPACE ts1
|
|
ADD DATAFILE 'datafile.dat'
|
|
USE LOGFILE GROUP lg1
|
|
ENGINE = ndb;
|
|
CREATE TABLE t1
|
|
(a INT NOT NULL PRIMARY KEY, b VARCHAR(20), c VARCHAR(20))
|
|
ENGINE = ndb;
|
|
ALTER TABLE t1 TABLESPACE ts1, MODIFY c CHAR(20) STORAGE DISK;
|
|
DROP TABLE t1;
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'datafile.dat';
|
|
DROP TABLESPACE ts1;
|
|
DROP LOGFILE GROUP lg1
|
|
ENGINE = ndb;
|
|
#test alter table add column with partion by and comment clause
|
|
CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
|
|
ALTER TABLE t1 ADD value INT NULL, COMMENT='comment1' PARTITION BY KEY(id);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
`value` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 COMMENT='comment1'
|
|
/*!50100 PARTITION BY KEY (id) */
|
|
DROP TABLE t1;
|
|
#test alter table with and without ENGINE clause
|
|
CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
|
|
ALTER TABLE t1 ENGINE=NDB PARTITION BY KEY(id);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY KEY (id) */
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(id INT, PRIMARY KEY(id)) ENGINE=NDB CHARSET=latin1;
|
|
ALTER TABLE t1 PARTITION BY KEY(id);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`id` int(11) NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
|
|
/*!50100 PARTITION BY KEY (id) */
|
|
DROP TABLE t1;
|
|
bug#24577931 table comment is case sensitive
|
|
create table tbasic(
|
|
id int not null,
|
|
name varchar(20), age int
|
|
) engine=ndb charset=latin1;
|
|
-- tbasic --
|
|
Version: Any
|
|
Fragment type: HashMapPartition
|
|
K Value: 6
|
|
Min load factor: 78
|
|
Max load factor: 80
|
|
Temporary table: no
|
|
Number of attributes: 4
|
|
Number of primary keys: 1
|
|
Length of frm data: XXX
|
|
Max Rows: 0
|
|
Row Checksum: 1
|
|
Row GCI: 1
|
|
SingleUserMode: 0
|
|
ForceVarPart: 1
|
|
PartitionCount: 8
|
|
FragmentCount: 8
|
|
PartitionBalance: FOR_RP_BY_LDM
|
|
ExtraRowGciBits: 0
|
|
ExtraRowAuthorBits: 0
|
|
TableStatus: Retrieved
|
|
Table options:
|
|
HashMap: DEFAULT-HASHMAP-3840-8
|
|
-- Attributes --
|
|
id Int NOT NULL AT=FIXED ST=MEMORY
|
|
name Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
|
|
age Int NULL AT=FIXED ST=MEMORY
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
-- Indexes --
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
|
|
NDBT_ProgramExit: 0 - OK
|
|
|
|
alter table tbasic algorithm=copy, comment="NDB_TABLE=FULLY_REPLICATEd=1";
|
|
-- tbasic --
|
|
Version: Any
|
|
Fragment type: HashMapPartition
|
|
K Value: 6
|
|
Min load factor: 78
|
|
Max load factor: 80
|
|
Temporary table: no
|
|
Number of attributes: 4
|
|
Number of primary keys: 1
|
|
Length of frm data: XXX
|
|
Max Rows: 0
|
|
Row Checksum: 1
|
|
Row GCI: 1
|
|
SingleUserMode: 0
|
|
ForceVarPart: 1
|
|
PartitionCount: 8
|
|
FragmentCount: 8
|
|
PartitionBalance: FOR_RP_BY_LDM
|
|
ExtraRowGciBits: 0
|
|
ExtraRowAuthorBits: 0
|
|
TableStatus: Retrieved
|
|
Table options: readbackup, fullyreplicated
|
|
HashMap: DEFAULT-HASHMAP-3840-8
|
|
-- Attributes --
|
|
id Int NOT NULL AT=FIXED ST=MEMORY
|
|
name Varchar(20;latin1_swedish_ci) NULL AT=SHORT_VAR ST=MEMORY
|
|
age Int NULL AT=FIXED ST=MEMORY
|
|
$PK Bigunsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
|
|
-- Indexes --
|
|
PRIMARY KEY($PK) - UniqueHashIndex
|
|
|
|
NDBT_ProgramExit: 0 - OK
|
|
|
|
drop table tbasic;
|