[GitHub] [shardingsphere] AdelaHong commented on issue #12427: MySQLSyntaxErrorException: Unknown column 'tt.DELETE_USER_NAME' in 'field list'

[GitBox]

AdelaHong commented on issue #12427:
URL: 
https://github.com/apache/shardingsphere/issues/12427#issuecomment-920057857


   I will provide the configuration again：
   
   `CREATE TABLE tf_b_bank_info(idbigint(20) NOT NULL AUTO_INCREMENT,bankno 
varchar(25) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 
COMMENT='';
   
   CREATE TABLE tf_b_bank_user (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   bank_id bigint(20) DEFAULT NULL COMMENT '银行类型:tf_b_bank_info的id',
   bankno varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '银行卡号',
   PRIMARY KEY (id)
   ) ENGINE=InnoDB AUTO_INCREMENT=178026 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 
COMMENT='';`
   ```
   
   execute sql statement：
   SELECT t.ID, t.BANKNO, tt.bankno banknono FROM tf_b_bank_user t JOIN 
tf_b_bank_info tt ON tt.id = t.bank_id`
   
   Execute the final result：
   When two tables have a bankno field at the same time, the bankno field of 
the two tables will be converted to DELETE_USER_NAME instead of only the bankno 
field of the specified table
   
   java api configuration :
   `@Configuration
   @AutoConfigureBefore(DruidDataSourceAutoConfigure.class)
   public class ShardingConfigurer {
   
   @Bean
   public DataSource masterDataSource() {
       return DruidDataSourceBuilder.create().build();
   }
   
   @Bean
   @DependsOn({"masterDataSource"})
   @Primary
   public DataSource dataSource(DataSource masterDataSource) throws Exception {
   
       Map<String,DataSource> dataSourceMap = new HashMap<>();
       dataSourceMap.put("ds_0", masterDataSource);
   
       Properties properties = new Properties();
       properties.setProperty(SQL_SHOW.getKey(), "true");
   
       return ShardingSphereDataSourceFactory.createDataSource(
               dataSourceMap,
               Arrays.asList(
                       createEncryptRuleConfiguration())
               , properties);
   }
   
   
   public EncryptRuleConfiguration createEncryptRuleConfiguration() {
       Map<String, ShardingSphereAlgorithmConfiguration> 
encryptAlgorithmConfigs = new LinkedHashMap<>();
       Properties encryptProperties = new Properties();
       encryptProperties.setProperty("aes-key-value", "123456");
       encryptAlgorithmConfigs.put("aes", new 
ShardingSphereAlgorithmConfiguration("AES", encryptProperties));
   
   
       Collection<EncryptTableRuleConfiguration> tables = new LinkedList<>();
   
       Collection<EncryptColumnRuleConfiguration> columns = new LinkedList<>();
   
       EncryptColumnRuleConfiguration columnRuleConfiguration = new 
EncryptColumnRuleConfiguration("BANKNO","bankno",null,"DELETE_USER_NAME 
","aes");
       columns.add(columnRuleConfiguration);
   
       EncryptTableRuleConfiguration result = new 
EncryptTableRuleConfiguration("tf_b_bank_user", columns);;
   
       EncryptRuleConfiguration encryptRuleConfiguration = new 
EncryptRuleConfiguration(tables,encryptAlgorithmConfigs,false);
       return encryptRuleConfiguration;
   }
   }`


-- 
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.

To unsubscribe, e-mail: notifications-unsubscr...@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org