Question about key generate when useing sharding-jdbc

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.

Apache License 2.0

19.78k stars 6.69k forks source link

Question about key generate when useing sharding-jdbc #12307

Closed CodingBingo closed 3 years ago

CodingBingo commented 3 years ago

I am develop on master branch these days. when I transfer from mycat to ShardignSphere, I found that if I insert a record into my table, it will return a very large id. In our case, we want to control all database config in one place, so, we put different tables in one file, some of them are just single table in single database, we prefer use mysql auto increment id. I am wandering if ShardingSphere can add an arguments to determine if using keyGenerate or not.

INSERT INTO `food` (`gmt_create`, `gmt_modified`, `name`, `photo`, `unit`, `school_id`)
VALUES( '2020-03-31 21:51:58', '2020-03-31 21:51:58', 'egg', NULL, NULL, 100082);

strongduanmu commented 3 years ago

@CodingBingo Single table will not use id generator.

CodingBingo commented 3 years ago

@CodingBingo Single table will not use id generator. I have a configuration file like this:
schemaName: feedDataSource
default: true

dataSources: ds_feed_0: url: jdbc:mysql://***/feeds dataSourceClassName: com.alibaba.druid.pool.DruidDataSource username: password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 minPoolSize: 1

rules:

!SHARDING tables: food: actualDataNodes: ds_feed_0.food

defaultDatabaseStrategy: none: defaultTableStrategy: none: defaultKeyGenerateStrategy: column: none keyGeneratorName: none props: sql-show: true
```
I enumerated all single tables in this config file to have full control of every table. I know that **ShardingSphere** can auto load table from physical database. 
```

tristaZero commented 3 years ago

@CodingBingo It looks a little strange. From my point, if users configure single tables in the configuration, these single tables are likely to be applied to these default strategies, e.g., defaultKeyGenerateStrategy. On the other hand, when ShardingSphere automatically loads single tables, which means they are really single tables (configured single tables approximately are viewed as one-shard sharding tables). In this case, database auto-increment property fits them. How do you think?

tristaZero commented 3 years ago

Hi @CodingBingo Could I have your WeChat connection? If it's possible, I am waiting for your response in my email (panjuan@apache.org). ;-)

CodingBingo commented 3 years ago

Hi @CodingBingo Could I have your WeChat connection? If it's possible, I am waiting for your response in my email (panjuan@apache.org). ;-)

I have send my wechat to your email, I will explaine why we have this problem in the wecaht.

strongduanmu commented 3 years ago

Hi @CodingBingo, this is a bug in 5.0.0-beta. You can use the latest master branch. This problem has been fixed.

tristaZero commented 3 years ago

It was fixed.