mirror of
https://github.com/MariaDB/server.git
synced 2026-02-27 21:19:04 +01:00
158 lines
4.4 KiB
Text
158 lines
4.4 KiB
Text
for master_1
|
|
for child2
|
|
child2_1
|
|
child2_2
|
|
child2_3
|
|
for child3
|
|
child3_1
|
|
child3_2
|
|
child3_3
|
|
|
|
drop and create databases
|
|
connection master_1;
|
|
DROP DATABASE IF EXISTS auto_test_local;
|
|
CREATE DATABASE auto_test_local;
|
|
USE auto_test_local;
|
|
connection child2_1;
|
|
DROP DATABASE IF EXISTS auto_test_remote;
|
|
CREATE DATABASE auto_test_remote;
|
|
USE auto_test_remote;
|
|
connection child2_2;
|
|
DROP DATABASE IF EXISTS auto_test_remote2;
|
|
CREATE DATABASE auto_test_remote2;
|
|
USE auto_test_remote2;
|
|
|
|
test select 1
|
|
connection master_1;
|
|
SELECT 1;
|
|
1
|
|
1
|
|
|
|
create table select test
|
|
connection master_1;
|
|
DROP TABLE IF EXISTS ta_l;
|
|
CREATE TABLE ta_l (
|
|
a INT,
|
|
b CHAR(1),
|
|
c DATETIME,
|
|
PRIMARY KEY(a)
|
|
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1
|
|
Warnings:
|
|
Warning 138 Spider table params in COMMENT or CONNECTION strings have been deprecated and will be removed in a future release. Please use table options instead.
|
|
Warning 138 Spider table params in COMMENT or CONNECTION strings have been deprecated and will be removed in a future release. Please use table options instead.
|
|
INSERT INTO ta_l (a, b, c) VALUES
|
|
(1, 'a', '2008-08-01 10:21:39'),
|
|
(2, 'b', '2000-01-01 00:00:00'),
|
|
(3, 'e', '2007-06-04 20:03:11'),
|
|
(4, 'd', '2003-11-30 05:01:03'),
|
|
(5, 'c', '2001-12-31 23:59:59');
|
|
|
|
direct_updating test
|
|
connection master_1;
|
|
SHOW STATUS LIKE 'Spider_direct_update';
|
|
Variable_name Value
|
|
Spider_direct_update 0
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-01 10:21:39
|
|
2 b 2000-01-01 00:00:00
|
|
3 e 2007-06-04 20:03:11
|
|
4 d 2003-11-30 05:01:03
|
|
5 c 2001-12-31 23:59:59
|
|
update all rows with function
|
|
UPDATE ta_l SET c = ADDDATE(c, 1);
|
|
SHOW STATUS LIKE 'Spider_direct_update';
|
|
Variable_name Value
|
|
Spider_direct_update 1
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
2 b 2000-01-02 00:00:00
|
|
3 e 2007-06-05 20:03:11
|
|
4 d 2003-12-01 05:01:03
|
|
5 c 2002-01-01 23:59:59
|
|
update by primary key
|
|
UPDATE ta_l SET b = 'x' WHERE a = 3;
|
|
SHOW STATUS LIKE 'Spider_direct_update';
|
|
Variable_name Value
|
|
Spider_direct_update 2
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
2 b 2000-01-02 00:00:00
|
|
3 x 2007-06-05 20:03:11
|
|
4 d 2003-12-01 05:01:03
|
|
5 c 2002-01-01 23:59:59
|
|
update by a column without index
|
|
UPDATE ta_l SET c = '2011-10-17' WHERE b = 'x';
|
|
SHOW STATUS LIKE 'Spider_direct_update';
|
|
Variable_name Value
|
|
Spider_direct_update 3
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
2 b 2000-01-02 00:00:00
|
|
3 x 2011-10-17 00:00:00
|
|
4 d 2003-12-01 05:01:03
|
|
5 c 2002-01-01 23:59:59
|
|
update by primary key with order and limit
|
|
UPDATE ta_l SET c = ADDDATE(c, 1) WHERE a < 4 ORDER BY b DESC LIMIT 1;
|
|
SHOW STATUS LIKE 'Spider_direct_update';
|
|
Variable_name Value
|
|
Spider_direct_update 4
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
2 b 2000-01-02 00:00:00
|
|
3 x 2011-10-18 00:00:00
|
|
4 d 2003-12-01 05:01:03
|
|
5 c 2002-01-01 23:59:59
|
|
delete by primary key with order and limit
|
|
DELETE FROM ta_l WHERE a < 4 ORDER BY c LIMIT 1;
|
|
SHOW STATUS LIKE 'Spider_direct_delete';
|
|
Variable_name Value
|
|
Spider_direct_delete 1
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
3 x 2011-10-18 00:00:00
|
|
4 d 2003-12-01 05:01:03
|
|
5 c 2002-01-01 23:59:59
|
|
delete by a column without index
|
|
DELETE FROM ta_l WHERE b = 'c';
|
|
SHOW STATUS LIKE 'Spider_direct_delete';
|
|
Variable_name Value
|
|
Spider_direct_delete 2
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
3 x 2011-10-18 00:00:00
|
|
4 d 2003-12-01 05:01:03
|
|
delete by primary key
|
|
DELETE FROM ta_l WHERE a = 3;
|
|
SHOW STATUS LIKE 'Spider_direct_delete';
|
|
Variable_name Value
|
|
Spider_direct_delete 3
|
|
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a;
|
|
a b date_format(c, '%Y-%m-%d %H:%i:%s')
|
|
1 a 2008-08-02 10:21:39
|
|
4 d 2003-12-01 05:01:03
|
|
|
|
deinit
|
|
connection master_1;
|
|
DROP DATABASE IF EXISTS auto_test_local;
|
|
connection child2_1;
|
|
DROP DATABASE IF EXISTS auto_test_remote;
|
|
connection child2_2;
|
|
DROP DATABASE IF EXISTS auto_test_remote2;
|
|
for master_1
|
|
for child2
|
|
child2_1
|
|
child2_2
|
|
child2_3
|
|
for child3
|
|
child3_1
|
|
child3_2
|
|
child3_3
|
|
|
|
end of test
|