federated.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

SELECT * FROM federated.t1 ORDER BY name ASC;
SELECT * FROM federated.t1 GROUP BY other;

# ::delete_row
DELETE FROM federated.t1 WHERE id = 5; 
SELECT * FROM federated.t1 WHERE id = 5;

# ::delete_all_rows
DELETE FROM federated.t1;
SELECT * FROM federated.t1 WHERE id = 5;
connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 ( 
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32),
    `other` varchar(20),
    PRIMARY KEY  (`id`) );

connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32), 
    `other` varchar(20),
    PRIMARY KEY  (`id`) )
  ENGINE="FEDERATED"
  DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, other) VALUES ('First Name', 11111);
INSERT INTO federated.t1 (name, other) VALUES ('Second Name', NULL);
INSERT INTO federated.t1 (name, other) VALUES ('Third Name', 33333);
INSERT INTO federated.t1 (name, other) VALUES (NULL, NULL);
INSERT INTO federated.t1 (name, other) VALUES ('Fifth Name', 55555);
INSERT INTO federated.t1 (name, other) VALUES ('Sixth Name', 66666);
INSERT INTO federated.t1 (name) VALUES ('Seventh Name');
INSERT INTO federated.t1 (name, other) VALUES ('Eigth Name', 88888);
INSERT INTO federated.t1 (name, other) VALUES ('Ninth Name', 99999);
INSERT INTO federated.t1 (other) VALUES ('fee fie foe fum');

SELECT * FROM federated.t1 WHERE other IS NULL;
SELECT * FROM federated.t1 WHERE name IS NULL;
SELECT * FROM federated.t1 WHERE name IS NULL and other IS NULL;
SELECT * FROM federated.t1 WHERE name IS NULL or other IS NULL;

UPDATE federated.t1
SET name = 'Fourth Name', other = 'four four four'
WHERE name IS NULL AND other IS NULL;

UPDATE federated.t1 SET other = 'two two two two' WHERE name = 'Second Name';
UPDATE federated.t1 SET other = 'seven seven' WHERE name like 'Sev%';
UPDATE federated.t1 SET name = 'Tenth Name' WHERE other like 'fee fie%';
SELECT * FROM federated.t1 WHERE name IS NULL OR other IS NULL ;
SELECT * FROM federated.t1;

# test multi-keys
connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL DEFAULT '',
    `other` varchar(20) NOT NULL DEFAULT '',
    PRIMARY KEY  (`id`),
    KEY nameoth (name, other) );

connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL DEFAULT '',
    `other` varchar(20) NOT NULL DEFAULT '',
    PRIMARY KEY  (`id`),
    KEY nameoth (name, other))
  ENGINE="FEDERATED" DEFAULT
  CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, other) VALUES ('First Name', '1111');
INSERT INTO federated.t1 (name, other) VALUES ('Second Name', '2222');
INSERT INTO federated.t1 (name, other) VALUES ('Third Name', '3333');
SELECT * FROM federated.t1 WHERE name = 'Second Name';
SELECT * FROM federated.t1 WHERE other = '2222';
SELECT * FROM federated.t1 WHERE name = 'Third Name';
SELECT * FROM federated.t1 WHERE name = 'Third Name' AND other = '3333';

connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int NOT NULL auto_increment,
    `name` char(32) NOT NULL DEFAULT '',
    `bincol` binary(1) NOT NULL,
    `floatval` decimal(5,2) NOT NULL DEFAULT 0.0,
    `other` int NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY nameoth(name, other),
    KEY bincol(bincol),
    KEY floatval(floatval));

# test other types of indexes
connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int NOT NULL auto_increment,
    `name` char(32) NOT NULL DEFAULT '',
    `bincol` binary(1) NOT NULL,
    `floatval` decimal(5,2) NOT NULL DEFAULT 0.0,
    `other` int NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY nameoth(name,other),
    KEY bincol(bincol),
    KEY floatval(floatval))
  ENGINE="FEDERATED"
  DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, bincol, floatval, other) 
  VALUES ('first', 0x65, 11.11, 1111);
INSERT INTO federated.t1 (name, bincol, floatval, other)
  VALUES ('second', 0x66, 22.22, 2222);
INSERT INTO federated.t1 (name, bincol, floatval, other)
  VALUES ('third', 'g', 22.22, 2222);
SELECT * FROM federated.t1;
SELECT * FROM federated.t1 WHERE name = 'second';
SELECT * FROM federated.t1 WHERE bincol= 'f';
SELECT * FROM federated.t1 WHERE bincol= 0x66;
SELECT * FROM federated.t1 WHERE bincol= 0x67;
SELECT * FROM federated.t1 WHERE bincol= 'g';
SELECT * FROM federated.t1 WHERE floatval=11.11;
SELECT * FROM federated.t1 WHERE name='third';
SELECT * FROM federated.t1 WHERE other=2222;
SELECT * FROM federated.t1 WHERE name='third' and other=2222;

# more multi-column indexes, in the primary key
connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
  `id` int NOT NULL auto_increment,
  `col1` int(10) NOT NULL DEFAULT 0,
  `col2` varchar(64) NOT NULL DEFAULT '',
  `col3` int(20) NOT NULL,
  `col4` int(40) NOT NULL,
  primary key (`id`, `col1`, `col2`, `col3`, `col4`),
  key col1(col1),
  key col2(col2),
  key col3(col3),
  key col4(col4));

connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
  `id` int NOT NULL auto_increment,
  `col1` int(10) NOT NULL DEFAULT 0,
  `col2` varchar(64) NOT NULL DEFAULT '',
  `col3` int(20) NOT NULL,
  `col4` int(40) NOT NULL,
  primary key (`id`, `col1`, `col2`, `col3`, `col4`),
  key col1(col1),
  key col2(col2),
  key col3(col3),
  key col4(col4))
  ENGINE="FEDERATED"
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (1, 'one One', 11, 1111);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (2, 'Two two', 22, 2222);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (3, 'three Three', 33, 33333);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (4, 'fourfourfour', 444, 4444444);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (5, 'five 5 five five 5', 5, 55555);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (6, 'six six Sixsix', 6666, 6);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (7, 'seven Sevenseven', 77777, 7777);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (8, 'eight eight eight', 88888, 88);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (9, 'nine Nine', 999999, 999999);
INSERT INTO federated.t1 (col1, col2, col3, col4) 
  VALUES (10, 'Tenth ten TEN', 1010101, 1010);

SELECT * FROM federated.t1 WHERE col2 = 'two two'; 
SELECT * FROM federated.t1 WHERE col2 = 'two Two'; 
SELECT * FROM federated.t1 WHERE id = 3; 
SELECT * FROM federated.t1 WHERE id = 3 AND col1 = 3; 
SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'Two two'; 
SELECT * FROM federated.t1 WHERE id = 4 AND col1 = 4 AND col2 = 'fourfourfour'; 
SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' 
  AND col3 = 5; 
SELECT * FROM federated.t1 WHERE id = 5 AND col2 = 'five 5 five five 5' 
  AND col3 = 5
  AND col4 = 55555;
SELECT * FROM federated.t1 WHERE id = 5 
  AND col2 = 'Two two' AND col3 = 22
  AND col4 = 33;
SELECT * FROM federated.t1 WHERE id = 5 
  AND col2 = 'five 5 five five 5' AND col3 = 5 
  AND col4 = 55555;
SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'five 5 five five 5')
  OR (col2 = 'three Three' AND col3 = 33);
SELECT * FROM federated.t1 WHERE (id = 5 AND col2 = 'Two two')
  OR (col2 = 444 AND col3 = 4444444);
SELECT * FROM federated.t1 WHERE id = 1 
  OR col1 = 10
  OR col2 = 'Two two' 
  OR col3 = 33
  OR col4 = 4444444;
SELECT * FROM federated.t1 WHERE id > 5; 
SELECT * FROM federated.t1 WHERE id >= 5; 
SELECT * FROM federated.t1 WHERE id < 5; 
SELECT * FROM federated.t1 WHERE id <= 5; 
SELECT * FROM federated.t1 WHERE id != 5; 
SELECT * FROM federated.t1 WHERE id > 3 AND id < 7; 
SELECT * FROM federated.t1 WHERE id > 3 AND id <= 7; 
SELECT * FROM federated.t1 WHERE id >= 3 AND id <= 7; 
SELECT * FROM federated.t1 WHERE id < 3 AND id <= 7; 
SELECT * FROM federated.t1 WHERE id < 3 AND id > 7; 
SELECT * FROM federated.t1 WHERE id < 3 OR id > 7; 
SELECT * FROM federated.t1 WHERE col2 = 'three Three'; 
SELECT * FROM federated.t1 WHERE col2 > 'one'; 
SELECT * FROM federated.t1 WHERE col2 LIKE 's%'; 
SELECT * FROM federated.t1 WHERE col2 LIKE 'si%'; 
SELECT * FROM federated.t1 WHERE col2 LIKE 'se%'; 
SELECT * FROM federated.t1 WHERE col2 NOT LIKE 'e%'; 
SELECT * FROM federated.t1 WHERE col2 <> 'one One'; 

# more multi-column indexes, in the primary key
connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
  `col1` varchar(8) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  `col3` varchar(20) NOT NULL DEFAULT '',
  `col4` varchar(40) NOT NULL DEFAULT '',
  primary key (`col1`, `col2`, `col3`, `col4`),
  key 3key(`col2`,`col3`,`col4`),
  key 2key (`col3`,`col4`),
  key col4(col4));

connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
  `col1` varchar(8) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  `col3` varchar(20) NOT NULL DEFAULT '',
  `col4` varchar(40) NOT NULL DEFAULT '',
  primary key (`col1`, `col2`, `col3`, `col4`),
  key 3key(`col2`,`col3`,`col4`),
  key 2key (`col3`,`col4`),
  key col4(col4))
  ENGINE="FEDERATED"
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';