MySQL 拆分字符串函数 SPLIT_STR 不适用于 LEFT JOIN。有什么问题吗？

MySQL Split String Function SPLIT_STR does not work with LEFT JOIN. Any issue?

提问by Binyamin

Create MySQL Split String Function SPLIT_STRfedecarg.com/.../mysql-split-string-function/

创建 MySQL 拆分字符串函数SPLIT_STRfedecarg.com/.../mysql-split-string-function/

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Run SQL:

运行 SQL：

SELECT t.en AS `type`, SPLIT_STR(l.en, ',', 1) AS city,
SPLIT_STR(l.en, ',', 2) AS country
FROM table1
JOIN table2
USING ( id )
LEFT JOIN table3 AS t ON table1.type = t.id
/* the next line has failure with SPLIT_STR */
LEFT JOIN table3 AS l ON table1.location = l.id
WHERE language.lang =  'en'
AND language.url =  'europe-countries'
LIMIT 1;

table1

表格1

id               | type            | location
-----------------+-----------------+-----------------
6BC45C02         | place           | london,england

table2

表2

id               | url
-----------------+-----------------
6BC45C02         | europe-countries

table3

表3

id               | en
-----------------+-----------------
london           | London
england          | England

Failed result:

失败的结果：

type             | city            | country
-----------------+-----------------+----------------
place            | NULL            | NULL

Expected result would be to return cityand country:

预期结果将是返回city和country：

type             | city            | country
-----------------+-----------------+-----------------
place            | London          | England

---

---

On checking if SPLIT_STRis working with simple SQL:

检查是否SPLIT_STR使用简单的 SQL：

SELECT SPLIT_STR(location, ',', 1) AS city, SPLIT_STR(location, ',', 2) AS contry
FROM table1
WHERE id =  '6BC45C02'
LIMIT 1;

it returns fine result:

它返回良好的结果：

city             | contry
-----------------+-----------------
london           | england

采纳答案by xQbert

Maybe this... but performance will be terrible.

也许这......但性能会很糟糕。

SELECT T1.type,
  SPLIT_STR(t.en, ',', 1) AS city,
  SPLIT_STR(l.en, ',', 2) AS country
FROM table1 t1
INNER JOIN table2 t2 
  ON T1.ID = T2.ID
LEFT JOIN table3 t 
  ON t.id = SPLIT_STR(t1.location, ',', 1)
LEFT JOIN table3 l 
  ON l.id = SPLIT_STR(t1.location, ',', 2)
WHERE t2.url = 'europe-countries'
LIMIT 1;

This would be better as it doesn't require calling the function 4 times: (used coalesce to determine if function isn't working as expected will return proper case, then lower case, then function broke if it's not working as expected)

这会更好，因为它不需要调用函数 4 次：（使用合并来确定函数是否没有按预期工作将返回正确的大小写，然后是小写，如果它没有按预期工作，则函数中断）

SELECT InTable.Type, 
   coalesce(t.en, inTable.City, 'FunctionBroke') as city, 
   coalesce(l.en, intable.country, 'FunctionBroke2') as country
FROM 
    (SELECT t1.type,
      SPLIT_STR(T1.Location, ',', 1) AS City,
      SPLIT_STR(T1.Location, ',', 2) AS Country
    FROM table1 T1
    INNER JOIN table2 T2 
      ON T1.ID = T2.ID 
        AND t2.url='europe-countries'
    ) InTable
LEFT JOIN table3 t 
  ON InTable.City = t.id
LEFT join table3 l 
  ON InTable.Country = l.id
LIMIT 1;

Still better exists: is table 3's only purpose to Proper case the city/country name; and is the only purpose of the UDF (user defined function) to split the values?

更好的是存在：表 3 的唯一目的是正确的城市/国家名称；是UDF（用户定义函数）拆分值的唯一目的吗？

• EDIT 1 Fixed first should have been t.en instead of l.en on first left join.
• EDIT 2 Fixed 2nd should have been t1 (that's copy and paste for you)
• EDIT 3 Relooked at everything in both responses copied several of the errors in the OP

• 编辑 1 首先固定应该是 t.en 而不是第一个左连接时的 l.en。
• EDIT 2 Fixed 2nd 应该是 t1（这是为您复制和粘贴）
• 编辑 3 重新审视了两个响应中的所有内容，复制了 OP 中的几个错误

回答by a1ex07

From what I see, SPLIT_STR(l.en, ',', 1)will always be null(there is nothing to split in table3.en) Also, joining condition for table table1.location = l.idalways false for your data (neither londonnor englandequals to london,england).

从我所见，SPLIT_STR(l.en, ',', 1)将永远是null（没有什么可拆分的table3.en）此外，表的连接条件table1.location = l.id对于您的数据始终为 false（既不london也不england等于london,england）。

Based on desired output you posted, I think you are looking for something like (I'm not sure what is language.url = 'europe-countries', I don't see table or alias named "language" in the question, so I just ignored it)

根据您发布的所需输出，我认为您正在寻找类似的东西（我不确定是什么language.url = 'europe-countries'，我在问题中没有看到名为“语言”的表或别名，所以我只是忽略了它）

SELECT t1.`type` AS `type`, 
MAX(CASE WHEN t3.id = SPLIT_STR(t1.location, ',', 1) THEN t3.en END) as `city`,
MAX(CASE WHEN t3.id = SPLIT_STR(t1.location, ',', 2) THEN t3.en END) as `country`
FROM table1 t1
INNER JOIN table2 t2 ON (t2.id = t1.id)
LEFT JOIN table3 t3 ON 
 (t3.id = SPLIT_STR(t1.location, ',', 1) OR t3.id = SPLIT_STR(t1.location, ',', 2))
GROUP BY t1.`type`

Updated(l.en replaced by t1.location)

更新（l.en 替换为t1.location）