lwtdev commented on issue #7548:
URL: https://github.com/apache/shardingsphere/issues/7548#issuecomment-698748895
Hi @tristaZero @xbkaishui I have test my scenarios , that work well.
- Test `select` 、 `create table` and `drop table` when table not exsits
before ss start.
```sql
mysql> show tables;
Empty set (0.01 sec)
mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql |
+-----------------+------------------------------------------+
| ds_01 | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist
mysql> CREATE TABLE `customer` (
-> `id` bigint(20) NOT NULL COMMENT '主键ID',
-> `party_id` bigint(20) NOT NULL COMMENT '用户ID',
-> `status` smallint(6) DEFAULT NULL COMMENT '状态：1-有效，0-无效 待删除字段',
-> PRIMARY KEY (`id`),
-> KEY `party_id_index` (`party_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表';
Query OK, 0 rows affected (0.44 sec)
mysql> show tables;
+---------------------------------+
| Tables_in_spsqltest_sharding_01 |
+---------------------------------+
| customer |
+---------------------------------+
1 row in set (0.01 sec)
mysql> insert into customer (id, party_id, status) values (1, 1, 1), (2, 1,
2), (3, 1, 3);
Query OK, 3 rows affected (0.03 sec)
mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql |
+-----------------+------------------------------------------+
| ds_01 | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from customer where id = 3;
+----+----------+--------+
| id | party_id | status |
+----+----------+--------+
| 3 | 1 | 3 |
+----+----------+--------+
1 row in set (0.01 sec)
mysql> drop table customer;
Query OK, 0 rows affected (0.05 sec)
mysql> show tables;
Empty set (0.01 sec)
mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql |
+-----------------+------------------------------------------+
| ds_01 | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist
```
- Test `select` 、 `create table` and `drop table` when table exsits before
ss start.
```sql
mysql> show tables;
+---------------------------------+
| Tables_in_spsqltest_sharding_01 |
+---------------------------------+
| customer |
+---------------------------------+
1 row in set (0.18 sec)
mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql |
+-----------------+------------------------------------------+
| ds_01 | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.28 sec)
mysql> select * from customer where id = 3;
+----+----------+--------+
| id | party_id | status |
+----+----------+--------+
| 3 | 1 | 3 |
+----+----------+--------+
1 row in set (0.01 sec)
mysql> drop table customer;
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
Empty set (0.01 sec)
mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql |
+-----------------+------------------------------------------+
| ds_01 | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
