【mysql】mycat 全局表

1.修改schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">
        <table name="my_test2" dataNode="dnmycatdb$1-9" rule="sharding-by-intfile-mycatdb-my_test2"></table>
        <table name="my_test3" primaryKey="jobid" type="global" dataNode="dnmycatdb$1-9" ></table>
</schema>

2.创建表

"sspu@192.168.1.11">show tables; 
+-------------------+
| Tables in mycatdb |
+-------------------+
| my_test2          |
| my_test3          |
+-------------------+
2 rows in set (0.00 sec)

"sspu@192.168.1.11">create table my_test3(id int,name varchar(20),jobid int);
Query OK, 0 rows affected (0.14 sec)

"sspu@192.168.1.11">desc my_test3; 
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id             | int(11)     | YES  |     | NULL    |       |
| name           | varchar(20) | YES  |     | NULL    |       |
| jobid          | int(11)     | YES  |     | NULL    |       |
| _mycat_op_time | bigint(20)  | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

3.插入数据

insert into  my_test3 (id,name,jobid) values(1,'xsq',1000);
insert into  my_test3 (id,name,jobid) values(2,'xsq2',1002);
insert into  my_test3 (id,name,jobid) values(3,'xsq3',1003);
insert into  my_test3 (id,name,jobid) values(4,'xsq4',1004);
insert into  my_test3 (id,name,jobid) values(5,'xsq5',1005);
insert into  my_test3 (id,name,jobid) values(6,'xsq6',1006);
insert into  my_test3 (id,name,jobid) values(7,'xsq7',1007);
insert into  my_test3 (id,name,jobid) values(8,'xsq8',1008);
insert into  my_test3 (id,name,jobid) values(9,'xsq9',1009);

"sspu@192.168.1.11">select * from my_test3;
+------+------+-------+----------------+
| id   | name | jobid | _mycat_op_time |
+------+------+-------+----------------+
|    1 | xsq  |  1000 |  1724219540528 |
|    2 | xsq2 |  1002 |  1724219540573 |
|    3 | xsq3 |  1003 |  1724219540591 |
|    4 | xsq4 |  1004 |  1724219540616 |
|    5 | xsq5 |  1005 |  1724219540629 |
|    6 | xsq6 |  1006 |  1724219540656 |
|    7 | xsq7 |  1007 |  1724219540675 |
|    8 | xsq8 |  1008 |  1724219540690 |
|    9 | xsq9 |  1009 |  1724219541941 |
+------+------+-------+----------------+
9 rows in set (0.02 sec)

my_test3 表多出一列。

4.查看表。

"sspu@192.168.1.11">select table_schema,table_name as "Tables",ROUND(((data_length +
    -> index_length) / 1024 / 1024), 2) "Size in MB" from information_schema.TABLES
    -> where TABLE_NAME = "my_test3" order by (data_length + index_length) desc;
+--------------+----------+------------+
| table_schema | Tables   | Size in MB |
+--------------+----------+------------+
| sspudb6      | my_test3 |       0.02 |
| sspudb5      | my_test3 |       0.02 |
| sspudb4      | my_test3 |       0.02 |
+--------------+----------+------------+
3 rows in set (0.01 sec)

--查看执行计划。
explain insert into  my_test3 (id,name,jobid) values(10,'xsq9',1010);

"sspu@192.168.1.11">explain insert into  my_test3 (id,name,jobid) values(10,'xsq9',1010);
+------------+-----------------------------------------------------------------------------------------+
| DATA_NODE  | SQL                                                                                     |
+------------+-----------------------------------------------------------------------------------------+
| dnmycatdb1 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb2 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb3 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb4 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb5 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb6 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb7 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb8 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
| dnmycatdb9 | insert into my_test3(id,name,jobid,_mycat_op_time) values(10,'xsq9',1010,1724219675287) |
+------------+-----------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
--所有节点均有。说明全局表设置成功。

5.总结 
全局表在所有解决均有。