天翼云分布式关系型数据库客常见操作
文档简介：
DML操作
mysql> insert into employee(employee_id,name,create_date) values(1, 'test1', now());
Query OK, 1 row affected (0.01 sec)
咨询热线：400-826-7010，为您提供专业的售前咨询，让您快速了解云产品，助您轻松上云！
微信咨询
免费试用、价格特惠
常见操作
DML操作
mysql> insert into employee(employee_id,name,create_date) values(1, 'test1', now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(employee_id,name,create_date) values(2, 'test2', now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(employee_id,name,create_date) values(3, 'test3', now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+-------------+-------+-------+---------------------+---------+
| employee_id | name | email | create_date | area_id |
+-------------+-------+-------+---------------------+---------+
| 2 | test2 | NULL | 2017-02-06 15:22:02 | NULL |
| 1 | test1 | NULL | 2017-02-06 15:21:55 | NULL |
| 3 | test3 | NULL | 2017-02-06 15:22:08 | NULL |
+-------------+-------+-------+---------------------+---------+
3 rows in set (0.00 sec)
mysql> update employee set name = 'test2_update' where employee_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+-------------+--------------+-------+---------------------+---------+
| employee_id | name | email | create_date | area_id |
+-------------+--------------+-------+---------------------+---------+
| 2 | test2_update | NULL | 2017-02-06 15:22:02 | NULL |
| 3 | test3 | NULL | 2017-02-06 15:22:08 | NULL |
| 1 | test1 | NULL | 2017-02-06 15:21:55 | NULL |
+-------------+--------------+-------+---------------------+---------+
3 rows in set (0.00 sec)
mysql> delete from employee where employee_id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+-------------+--------------+-------+---------------------+---------+
| employee_id | name | email | create_date | area_id |
+-------------+--------------+-------+---------------------+---------+
| 3 | test3 | NULL | 2017-02-06 15:22:08 | NULL |
| 2 | test2_update | NULL | 2017-02-06 15:22:02 | NULL |
+-------------+--------------+-------+---------------------+---------+
2 rows in set (0.00 sec)
mysql> insert into employee(employee_id,name,create_date) values(1, 'test1', now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from employee;
+-------------+--------------+-------+---------------------+---------+
| employee_id | name | email | create_date | area_id |
+-------------+--------------+-------+---------------------+---------+
| 2 | test2_update | NULL | 2017-02-06 15:22:02 | NULL |
| 1 | test1 | NULL | 2017-02-06 15:24:32 | NULL |
| 3 | test3 | NULL | 2017-02-06 15:22:08 | NULL |
+-------------+--------------+-------+---------------------+---------+
3 rows in set (0.01 sec)
mysql>
-
使用全局序列
使用全局序列
数据分布式存放后，原来数据库的自增序列无法满足全局唯一的要求，这时就需要使用全局序列
1. 在schema管理页面，点选待建表schema的“管理”链接
2. 在schema基本信息页面，点选“全局序列管理”菜单
3. 点击“新增序列”按钮
4. 填写全局序列信息
5. 使用全局序列
mysql> delete from employee;
Query OK, 3 rows affected (0.01 sec)
mysql> insert into employee(employee_id,name,create_date) values(SEQ_TEST.nextval, 'test1', now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(employee_id,name,create_date) values(SEQ_TEST.nextval, 'test2', now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into employee(employee_id,name,create_date) values(SEQ_TEST.nextval, 'test3', now());
Query OK, 1 row affected (0.01 sec)
mysql> select SEQ_TEST.nextval;
+------------------+
| SEQ_TEST.nextval |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)
mysql>
-
查看数据路由
查看数据路由
可以通过route命令查看数据所在的分片，可以看到employee表数据分布在dn1、dn2、dn3三个数据分片上， employee_id=1的记录存放在dn2上，employee_id=2的记录存放在dn3上，
employee_id=3的记录存放在dn1上
mysql> route select * from employee;
+-----------+-----------------------------------+---------+------------+
| DATA_NODE | SQL | G_INDEX | TABLE_TYPE |
+-----------+-----------------------------------+---------+------------+
| dn1 | SELECT * FROM employee LIMIT 1000 | -- | SHARDING |
| dn2 | SELECT * FROM employee LIMIT 1000 | -- | SHARDING |
| dn3 | SELECT * FROM employee LIMIT 1000 | -- | SHARDING |
+-----------+-----------------------------------+---------+------------+
3 rows in set (0.00 sec)
mysql> route select * from employee where employee_id=1;
+-----------+--------------------------------------------+---------+------------+
| DATA_NODE | SQL | G_INDEX | TABLE_TYPE |
+-----------+--------------------------------------------+---------+------------+
| dn2 | select * from employee where employee_id=1 | -- | SHARDING |
+-----------+--------------------------------------------+---------+------------+
1 row in set (0.00 sec)
mysql> route select * from employee where employee_id=2;
+-----------+--------------------------------------------+---------+------------+
| DATA_NODE | SQL | G_INDEX | TABLE_TYPE |
+-----------+--------------------------------------------+---------+------------+
| dn3 | select * from employee where employee_id=2 | -- | SHARDING |
+-----------+--------------------------------------------+---------+------------+
1 row in set (0.00 sec)
mysql> route select * from employee where employee_id=3;
+-----------+--------------------------------------------+---------+------------+
| DATA_NODE | SQL | G_INDEX | TABLE_TYPE |
+-----------+--------------------------------------------+---------+------------+
| dn1 | select * from employee where employee_id=3 | -- | SHARDING |
+-----------+--------------------------------------------+---------+------------+
1 row in set (0.00 sec)
mysql>
-
使用管理命令
使用管理命令
为方便用户使用和维护UDAL ，UDAL提供自己特有的管理命令，包括：配置查看、状态查看、统计监控、序列运维、索引运维等。 UDAL的管理命令以UDAL开头， 如：查看版本号udal show version, 查看帮助udal show help; 详细的管理命令说明请参考管理命令说明
