提问人：TV-C-1-5 提问时间：5/2/2023 更新时间：5/2/2023 访问量：64
MySQL - 截断了不正确的 DOUBLE 值 - 在逗号分隔的 VARCHAR 列上使用 SELECT IN（）
MySQL - Truncated incorrect DOUBLE value - using SELECT IN() on comma separated VARCHAR column
问：
我有一个带有 VARCHAR 列的 MySQL 表，该列保存了多个或单个访客 ID 值。我希望ENTERED_TEXT具有一对多关系，并且不想为该关系与VISITOR_ID创建多行 - 因此，我选择在VISITOR_ID列中添加多个 id。我现在想在该列中搜索多个相关 ID。VISITOR_ID
| VISITOR_ID | ENTERED_TEXT |
------------------------------------------
| 123,133,777 | text text |
| 555 | text text text |
| 444,133,777 | text |
| 999 | text text text text text |
当我尝试将选择与 IN（） 一起使用时
SELECT *
FROM `My_Table`
WHERE
VISITOR_ID IN(444,777)
我只得到一行和一个错误：
| 444,133,777 |
警告：#1292 截断了不正确的 DOUBLE 值“123,133,777”
我希望这两行返回：
| 444,133,777 |
| 123,133,777 |
还：
如果我使用：
SELECT *
FROM `My_Table`
WHERE
VISITOR_ID IN(555,999)
我取回了包含单个值的 2 行：
| 555 |
| 999 |
我想我理解逗号分隔的值没有被我的 IN（） 子句视为单独的值，而是被看作是这样的“123,133,777”——但我如何将这些值读取为适合我的 IN（） 搜索的单独值？
我正在尝试更改列类型，以及拆分值的各种方法。不确定最好的方法是什么。 我也明白我可以通过对VISITOR_ID使用单独的行来解决这个问题 - 我正在考虑。只是我认为让这些多个 ID 共享用户输入的相似的单个文本值是有意义的。似乎 1 条记录会比许多记录更好？不确定这是要走的路。
答：
2赞
Bill Karwin
5/2/2023
#1
MySQL具有FIND_IN_SET（）函数，可以做您想要的，但这不是它的预期用途。它应该用于搜索 MySQL 的 SET 数据类型的值。
实际上，它会搜索逗号分隔的字符串，并返回列表中找到值的位置。
mysql> select find_in_set(777, '123,133,777') as loc;
+-----+
| loc |
+-----+
| 3 |
+-----+
它有缺点：它只能一次搜索一个值，因此要搜索多个值，您必须使用多个搜索词：
mysql> select find_in_set(777, '123,133,777') or find_in_set(444, '123,133,777') as loc;
此外，它还破坏了使用索引的任何机会。这意味着您的查询将具有非常糟糕的性能，因为它们必须执行表扫描。
此外，它对空间也很挑剔。如果你有一个逗号分隔的字符串，里面有这样的空格，它根本无法按你预期的方式工作，因为只有当你搜索一个包含类似 的空格的字符串时，它才会匹配。'123, 133, 777'' 777'
对于一对多关系，最好正确地规范化数据（正如 GMB 上面的评论所说），并在每行存储一个值。然后，搜索的编码要简单得多，并且可以利用索引来优化查询。
这是我们不鼓励人们将逗号分隔的列表存储在字符串中的众多原因之一。
评论
0赞
TV-C-1-5
5/2/2023
这似乎是一些可靠的建议。我凭直觉知道分离的值会导致问题 - 我只是无法清楚地看到它们。树木的森林。看起来您正在保存一些解压缩和编码，以通过在一列中使用多个 ID 来更新文本 - 但毕竟......也许不是。
2赞
Bill Karwin
5/2/2023
只需将 SQL 值视为标量即可。没有所谓的“列表”——你所拥有的是一个标量字符串，它恰好包含数字和逗号。仅当您计划将整个列表存储和获取为单个值时，才可以将逗号分隔的“列表”存储为字符串。不要尝试使用 SQL 表达式来处理列表的各个元素。
0赞
TV-C-1-5
5/2/2023
那么，我该如何处理这里的答案选择呢？到目前为止，这两个答案都解决并解决了这个问题。但正确的答案可能是 - “不要将 CSV 列表存储在数据库列中”
1赞
Bill Karwin
5/2/2023
我不能建议你如何奖励问题。我已经因为这样做而受到 SO 管理员的责备。
1赞
SelVazi
5/2/2023
#2
这是一种通过将逗号分隔的列拆分为行来做到这一点的方法，然后将条件应用于这些生成的行：json_table
with cte as (
select t.VISITOR_ID, j.VISITOR
from mytable t
join json_table(
CONCAT('[', t.VISITOR_ID, ']'),
'$[*]' columns (VISITOR BIGINT(20) path '$')
) j
)
select distinct VISITOR_ID
from cte
where VISITOR IN(444,777);
评论