我有以下结构:
CREATE TABLE `proxies` (
`proxy_id` int(11) NOT NULL AUTO_INCREMENT,
`proxy_ip` varchar(50) DEFAULT NULL,
`proxy_port` smallint(5) unsigned DEFAULT NULL,
`proxy_country` varchar(50) DEFAULT NULL,
PRIMARY KEY (`proxy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `accounts` (
`account_id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`ip_range` text,
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我在proxies
桌上有3行,其中包含德国,芬兰和法国的国家名称。 accounts
表中的ip_range
字段包含,
字符隔开的proxy_id
列表。
现在我有这样的查询:
SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN (`ip_range`)
GROUP BY `account_id`
我期望这样的事情:
account_id | username | password | ip_range | countries |
---|---|---|---|---|
1 | name | pass | 1,2,3 | Germany,France,Finland |
但是我得到的是:
account_id | username | password | ip_range | countries |
---|---|---|---|---|
1 | name | pass | 1,2,3 | Germany |
正如我注意到的那样,ip_range
会以文字的形式施放,因此我的查询就像
SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN ('1,2,3')
GROUP BY `account_id`
那是错的。它应该是:
SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON `proxy_id` IN (1,2,3)
GROUP BY `account_id`
我该如何修复?
分析解答
对于IN
,您需要单个值,对于搜索字符串,您使用FIND_IN SET
如下
SELECT accounts.*, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON FIND_IN_SET(`proxy_id` , `ip_range`)
GROUP BY `account_id`
但是您应该读取在数据库列中存储一个划界列表真的很糟糕吗?并将表结构归一化,每个字符串函数都很慢。
正如Erwin指出的那样,您的代码不是很好,因为FULL_GROUP会导致错误,并且由于MySQL 8默认情况下会激活。 阅读有关GROUP的更多信息
因此,更好的解决方案将是
SELECT accounts.account_id,accounts.username,accounts.password
,accounts.ip_range, GROUP_CONCAT(proxy_country) countries
FROM accounts
LEFT JOIN `proxies` ON FIND_IN_SET(`proxy_id` , `ip_range`)
GROUP BY accounts.account_id,accounts.username
,accounts.password, accounts.ip_range
SELECT *
是一个坏习惯,也应该避免。