对比字符串str1（逗号分隔）字符串str2(逗号分隔),看是否有交易
发斯蒂芬发斯蒂芬阿斯顿发送到发斯蒂芬的撒发的发撒的沙发打的费sad发的发生的范德萨发三大法师的发的发的发撒的发斯蒂芬阿斯顿发斯蒂芬啊
样本数据如下：
DROP TABLE IF EXISTS `compare_table`;
CREATE TABLE `compare_table` (
`id` int(255) UNSIGNED NOT NULL AUTO_INCREMENT,
`str2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`str1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of compare_table
-- ----------------------------
INSERT INTO `compare_table` VALUES (1, '1,3,4,', '6,3,3,2,0,');
INSERT INTO `compare_table` VALUES (2, 'a,d,e,', 'dd,b,33,');
INSERT INTO `compare_table` VALUES (3, 'ee,ff,gg,', 'e,f,g,');
问题：对比str1 和str2，如果有交集返回1，否则返回0
sql语句如下：
SELECT
str1,
str2,
CONCAT(',',str1) AS str1_concat,
concat(
LEFT (CONCAT( ',(', REPLACE ( str2, ',', '|' ) ), length( CONCAT( ',(', REPLACE ( str2, ',', '|' ) ) ) - 1 ), '),' ) AS str2_concat，
CONCAT(',',str1) regexp concat(LEFT (
CONCAT( ',(', REPLACE ( str2, ',', '|' ) ),length( CONCAT( ',(', REPLACE ( str2, ',', '|' ) ) ) - 1 ),'),' ) as str_duibi_result
FROM
compare_table
mysql 默认是不区分大小写的，如果想要区分大小写，请在 regexp 后面加上关键字 binary
运行结果如图：
- 发表于 2019-12-31 20:46
- 阅读 ( 52 )
