CREATE TEMPORARY TABLE if not exists tally(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
truncate table tally;
INSERT INTO tally (n) SELECT NULL FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b ; with cols as ( select COLUMN_NAME,DATA_TYPE,ordinal_position from information_schema.`COLUMNS` where TABLE_SCHEMA=@db_name and TABLE_NAME=@table_name ) , c as ( select SUBSTRING_INDEX(SUBSTRING_INDEX(COLUMN_NAME, '_', n.n), '_', -1) word, COLUMN_NAME,n from cols CROSS JOIN tally n where n.n <= 1 + (CHAR_LENGTH(COLUMN_NAME) - CHAR_LENGTH(REPLACE(COLUMN_NAME, '_', ''))) ), cd as ( select if(n>1, concat(UPPER(left(word,1)),substring(word,2,(length(word)-1))), word ) word,COLUMN_NAME,n from c ), cn as ( select group_concat(word order by n separator '') varName,COLUMN_NAME from cd group by COLUMN_NAME ), vct as ( select varName,cn.COLUMN_NAME,DATA_TYPE from cn left join cols on cn.COLUMN_NAME=cols.COLUMN_NAME ), map as ( select 'varchar' as dbType,'String' as javaType union select 'timestamp' as dbType,'Date' as javaType union select 'datetime' as dbType,'Date' as javaType union select 'date' as dbType,'Date' as javaType union select 'tinyint' as dbType,'Integer' as javaType union select 'int' as dbType,'Integer' as javaType union select 'smallint' as dbType,'Integer' as javaType union select 'year' as dbType,'Integer' as javaType union select 'bigint' as dbType,'Long' as javaType union select 'mediumint' as dbType,'Long' as javaType union select 'float' as dbType,'Float' as javaType union select 'double' as dbType,'Double' as javaType union select 'bit' as dbType,'Boolean' as javaType union select 'decimal' as dbType,'BigDecimal' as javaType union select 'char' as dbType,'String' as javaType union select 'tinytext' as dbType,'String' as javaType union select 'mediumtext' as dbType,'String' as javaType union select 'longtext' as dbType,'String' as javaType union select 'json' as dbType,'String' as javaType union select 'enum' as dbType,'String' as javaType union select 'text' as dbType,'String' as javaType ), vctm as ( select varName,COLUMN_NAME,DATA_TYPE,javaType from vct left join map on vct.DATA_TYPE=map.dbType ), cl as ( select concat(javaType,' ', varName, ';') from vctm inner join cols on vctm.COLUMN_NAME = cols.COLUMN_NAME ORDER BY cols.ordinal_position ) select * from cl;
CREATE TABLE `user` ( `username` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `role` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, UNIQUE INDEX `username_UNIQUE`(`username`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; INSERT INTO `user` VALUES ('u1', '[\"a\",\"b\"]'); INSERT INTO `user` VALUES ('u2', '[\"b\",\"c\"]'); INSERT INTO `user` VALUES ('u3', '[\"c\",\"d\"]');
所以表里数据是这样的。 username
role
u1
[“a”,”b”]
u2
[“b”,”c”]
u3
[“c”,”d”]
如果我需要查询拥有 角色a 或者 角色d 的用户,就可以这样查询:
1 2 3 4 5 6 7 8
mysql> select * from `user` where JSON_OVERLAPS(role,'["a","d"]')=1; +----------+-----------+ username role +----------+-----------+ u1 ["a","b"] u3 ["c","d"] +----------+-----------+ 2 rows in set (0.00 sec)
# The number of milliseconds of each tick tickTime=2000 # The number of ticks that the initial # synchronization phase can take initLimit=10 # The number of ticks that can pass between # sending a request and getting an acknowledgement syncLimit=5 # the directory where the snapshot is stored. # do not use /tmp for storage, /tmp here is just # example sakes. dataDir=/tmp/zookeeper # the port at which the clients will connect clientPort=2181 # ... PrometheusMetricsProvider #metricsProvider.httpPort=7000 #metricsProvider.exportJvmInfo=true
mysql> select * from article where 1 member of(catalogs); +----+-------+----------+ id title catalogs +----+-------+----------+ 1 t1 [1] 2 t2 [1,2] +----+-------+----------+ 2 rows in set (0.00 sec)
我们看到 [1] 和 [1,2] 都是能正确返回的。[11]则能正确的被过滤掉。
值的注意的是,如果你的内容是字符串类的表达的,要加上’’去匹配。注意看下面的区别:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
mysql> select 1 member of ('["1"]'); +-----------------------+ 1 member of ('["1"]') +-----------------------+ 0 +-----------------------+ 1 row in set (0.00 sec)
mysql> select "1" member of ('["1"]'); +-------------------------+ "1" member of ('["1"]') +-------------------------+ 1 +-------------------------+ 1 row in set (0.00 sec)
mysql> select 1 member of ('[1]'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'of ('[1]')' at line 1 mysql> select version(); +-----------+ version() +-----------+ 5.7.30 +-----------+
Properties props = new Properties(); props.put("bootstrap.servers", "192.168.1.102:9092"); props.put("group.id", "my-group"); props.put("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); props.put("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); KafkaConsumer<String, String> consumer = new KafkaConsumer<String, String>(props);
List<String> list = new ArrayList<>(); list.add("TEST1"); consumer.subscribe(list);
while (true){ ConsumerRecords<String, String> records = consumer.poll(Duration.ofMillis(100)); for (ConsumerRecord<String, String> record : records) { logger.info("{},{}",record.topic(),record.value()); } }
Properties props = new Properties(); props.put("bootstrap.servers", "192.168.1.102:9092"); props.put("group.id", "my-group"); props.put("key.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); props.put("value.deserializer", "org.apache.kafka.common.serialization.StringDeserializer"); KafkaConsumer<String, String> consumer = new KafkaConsumer<String, String>(props);
List<String> list = new ArrayList<>(); list.add("TEST1"); consumer.subscribe(list);
long start = getStart(); // 按实际需要设置开始的时间戳 Map<TopicPartition, Long> timestampsToSearch = new HashMap<>(); for(TopicPartition topicPartition:consumer.assignment()){ timestampsToSearch.put(topicPartition,start); }
Map<TopicPartition, OffsetAndTimestamp> offsetAndTimestampMap = consumer.offsetsForTimes(timestampsToSearch); for(Map.Entry<TopicPartition, OffsetAndTimestamp> entry:offsetAndTimestampMap.entrySet()){ if(entry.getValue()!=null) { consumer.seek(entry.getKey(), entry.getValue().offset()); }else{ // make some log } }
while (true){ ConsumerRecords<String, String> records = consumer.poll(Duration.ofMillis(100)); for (ConsumerRecord<String, String> record : records) { logger.info("{},{}",record.topic(),record.value()); } }