-+---------+-------+
| 1 | 1 | j2ee |
| 2 | 2 | mysql |
+----+---------+-------+
2 rows in set (0.00 sec)
mysql> delete from tag where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> use blog_article;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> insert into article(id,user_id,title,publish_time,create_time,update_time,category_id) \
-> values (1,1,'test1',now(),now(),now(),1),(2,1,'test2',now(),now(),now(),2),\
-> (3,1,'test3',now(),now(),now(),3),(4,2,'test4',now(),now(),now(),3);
Query OK, 4 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from article;
+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+
| id | user_id | title | abstract | content | publish_time | create_time | update_time | category_id |
+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+
| 4 | 2 | test4 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 3 |
| 1 | 1 | test1 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 1 |
| 2 | 1 | test2 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2 |
| 3 | 1 | test3 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 3 |
+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+
4 rows in set (0.00 sec)
mysql> update article set title = "new title" where user_id = 1 and id =2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
测试sequence生成： mysql> INSERT INTO MYCAT_SEQUENCE VALUES ('article_seq', 1, 1);
1 row in set (0.06 sec)
mysql> SELECT MYCAT_SEQ_CURRVAL('article_seq');
+----------------------------------+
| MYCAT_SEQ_CURRVAL('article_seq') |
+----------------------------------+
| 1,1 |
+----------------------------------+
1 row in set (0.01 sec)
mysql> SELECT MYCAT_SEQ_SETVAL('article_seq', 2);
+------------------------------------+
| MYCAT_SEQ_SETVAL('article_seq', 2) |
+------------------------------------+
| 2,1 |
+------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT MYCAT_SEQ_CURRVAL('article_seq');
+----------------------------------+
| MYCAT_SEQ_CURRVAL('article_seq') |
+----------------------------------+
| 2,1 |
+----------------------------------+
1 row in set (0.01 sec)
mysql> SELECT MYCAT_SEQ_NEXTVAL('article_seq');
+----------------------------------+
| MYCAT_SEQ_NEXTVAL('article_seq') |
+----------------------------------+
| 3,1 |
+----------------------------------+
1 row in set (0.01 sec)
mysql> SELECT MYCAT_SEQ_NEXTVAL('article_seq');
+----------------------------------+
| MYCAT_SEQ_NEXTVAL('article_seq') |
+----------------------------------+
| 4,1 |
+----------------------------------+
1 row in set (0.02 sec)
总结 mycat提供了数据库垂直拆分和水平拆分的方案，对于数据迁移可以按照id，user_id进行拆分。
也可以将数据分库存储，不拆分，但必须放到不同的库中（和水平拆分的分开）。
同时，如果进行拆分，则需要修改主键生成方式，目前看到mycat中提供一个sequence的存储过程，可以直接使用。
|