在MySQL中与字符串一起使用的explode()函数的等效函数是什么？

Question

在MySQL中，我想要搜索'31 - 7'，当另一个值为'7 - 31'时。我该使用什么语法来拆分MySQL中的字符串？在PHP中，我可能会使用explode(' - ',$string)并将它们组合在一起。是否有一种方法可以在MySQL中实现这一点？

背景：我正在处理体育比分，并希望尝试得分相同（日期也相同）的比赛-每个队伍列出的得分与他们对手的数据库记录相反。

理想的MySQL调用应该是：

Where opponent1.date  = opponent2.date
  AND opponent1.score = opponent2.score

（opponent2.score需要反过来变成opponent1.score）。

- Bob Cavezza

---

从外观上看，我认为你的数据库设计可能存在一些根本性的缺陷。你能否向我们展示相关表的架构以及它们的样本数据？ - Andrew Marshall

现在是不是该接受答案了？ - Arman P.

Answer 1

MYSQL没有内置像explode()一样的函数。但是您可以轻松地向您的DB添加类似的函数，然后从php查询中使用它。该函数将如下所示：

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '');

使用方法：

SELECT SPLIT_STRING('apple, pear, melon', ',', 1)

上面的例子将返回apple。 我认为在MySQL中不可能返回数组，所以必须明确指定要返回的出现位置 pos。如果你成功使用它，请告诉我。

- Arman P.

---

分隔符参数的数据类型大小VARCHAR（12）应该大于12，这样它就不会在使用更大的分隔符时产生不可预测的结果。 - doc_id

我需要在我的一个服务器中将这个函数声明为DETERMINISTIC。就像这样： RETURNS VARCHAR(255) DETERMINISTIC RETURN - nerkn

在执行上述查询之前，如果出现“此函数没有确定性”的错误，请使用以下命令：SET GLOBAL log_bin_trust_function_creators = 1; - prashant

不错的函数，但是如果你使用多字节字符集，你应该用CHAR_LENGTH()替换LENGTH()。 - Boolean_Type

@Boolean_Type谢谢，我没有考虑到占据可变长度的Unicode字符/符号。 - Arman P.

Answer 2

我尝试使用SUBSTRING_INDEX(string,delimiter,count)

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'

在mysql.com上查看更多信息 http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index

- madde74

---

这似乎很简单，在www.www.www.www.example.com上运行良好，以便仅获取example.com。 - ftrotter

Answer 3

您可以以这种方式使用存储过程..

DELIMITER |

CREATE PROCEDURE explode( pDelim VARCHAR(32), pStr TEXT)                                
BEGIN                                
  DROP TABLE IF EXISTS temp_explode;                                
  CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, word VARCHAR(40));                                
  SET @sql := CONCAT('INSERT INTO temp_explode (word) VALUES (', REPLACE(QUOTE(pStr), pDelim, '\'), (\''), ')');                                
  PREPARE myStmt FROM @sql;                                
  EXECUTE myStmt;                                
END |   

DELIMITER ;

• 示例调用：

 SET @str  = "The quick brown fox jumped over the lazy dog"; 
 SET @delim = " "; 

CALL explode(@delim,@str);
SELECT id,word FROM temp_explode;

- Disha Goyal

---

我不确定这是非常聪明还是非常危险。 - Dexter

非常聪明 - Stephen Mudere

我需要在MySQL中执行与SQL-SERVER相同的操作，使用以下语句：SELECT * FROM dbo.SPLIT_INT('1414,1412,1416,1411,1415,1413',',')。 - Francisco Souza

Answer 4

首先，您应该更改数据库结构 - 在这种情况下，得分是一种组合值，并应存储在两个列中，例如score_host和score_guest。

---

MySQL不提供explode()等功能，但在这种情况下，您可以使用SUBSTRING()和LOCATE()来截取主机和客人的得分。

SELECT 
   CONVERT(SUBSTRING(score, 1, LOCATE('-',score) - 2) USING INTEGER) as score_host,
   CONVERT(SUBSTRING(score, LOCATE('-',score)+2) USING INTEGER) as score_guest
FROM ...;

CONVERT() 用于将字符串 "23" 转换为数字 23。

- Crozin

Answer 5

使用这个函数。它非常有效。

将|替换为要分解/拆分的字符，值1、2、3等是基于数据集中条目数量：

Value_ONE|Value_TWO|Value_THREE

SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 1), '|', -1) AS PSI,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 2), '|', -1) AS GPM,
SUBSTRING_INDEX(SUBSTRING_INDEX(`tblNAME`.`tblFIELD`, '|', 3), '|', -1) AS LIQUID

我希望这可以帮到您。

- Sergio Rodriguez

---

你如何获取集合中的项目数量？ - Lucien

很遗憾，你需要事先知道值的数量。在我的情况下，我知道我需要三个值，并将它们重命名为PSI、GPM和LIQUID。如果数据集中的一个值为空，SQL会返回一个空值而不会出现错误。这样，我总是打印/回显$return->PSI、$return->GPM和$return->LIQUID。 - Sergio Rodriguez

@Lolums 你可以统计分隔符的出现次数：ROUND((CHAR_LENGTH(tblNAME.tblFIELD) - CHAR_LENGTH(REPLACE(tblNAME.tblFIELD, "|", ""))) / CHAR_LENGTH("|")) 零=长度为一个项目（没有分隔符），一=有两个项目，以此类推。 - FrancescoMM

Answer 6

使用substring_index，以下示例中创建了一个带有列score1和score2的表格，score1具有3-7，score2 7-3等，如图所示。下面的查询能够使用“ - ”拆分并反转score2的顺序并与score1进行比较。

SELECT CONCAT(
  SUBSTRING_INDEX(score1, '-', 1),
  SUBSTRING_INDEX(score1,'-',-1)
) AS my_score1,
CONCAT(
  SUBSTRING_INDEX(score2, '-', -1),
  SUBSTRING_INDEX(score2, '-', 1)
) AS my_score2
FROM test HAVING my_score1=my_score2

- philip mudenyo

Answer 7

这实际上是选定答案的修改版本，以支持Unicode字符，但我声望不够，无法在那里进行评论。

CREATE FUNCTION SPLIT_STRING(str VARCHAR(255) CHARSET utf8, delim VARCHAR(12), pos INT) RETURNS varchar(255) CHARSET utf8
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
       delim, '')

修改内容如下：

• 第一个参数设置为utf8
• 函数被设置为返回utf8
• 该代码使用CHAR_LENGTH()而不是LENGTH()来计算字符长度，而不是字节长度.

- vensires

Answer 8

正如@arman-p所指出的，MYSQL没有explode()函数。然而，我认为所提出的解决方案比必要的复杂得多。当您收到逗号分隔的列表字符串（例如，要查找的表键列表）时，您可以进行快速检查：

SELECT 
    table_key, field_1, field_2, field_3
FROM
    my_table
WHERE
    field_3 = 'my_field_3_value'
    AND (comma_list = table_key
        OR comma_list LIKE CONCAT(table_key, ',%')
        OR comma_list LIKE CONCAT('%,', table_key, ',%')
        OR comma_list LIKE CONCAT('%,', table_key))

假设你也需要在表中检查field_3。如果不需要它，请不要添加该条件。

- Al Zziwa

Answer 9

如果使用explode和foreach一起构建新字符串，可以通过使用while循环来模拟explode，如下所示：

CREATE FUNCTION explode_and_loop(sep VARCHAR(),inputstring VARCHAR()) RETURNS VARCHAR() 
BEGIN
    DECLARE part,returnstring VARCHAR();
    DECLARE cnt,partsCnt INT();
    SET returnstring = '';
    SET partsCnt = ((LENGTH(inputstring ) - LENGTH(REPLACE(inputstring,sep,''))) DIV LENGTH(sep);
    SET cnt = 0;
    WHILE cnt <= partsCnt DO
        SET cnt = cnt + 1;
        SET part = SUBSTRING_INDEX(SUBSTRING_INDEX(inputstring ,sep,cnt),sep,-1);
        -- DO SOMETHING with the part eg make html:
        SET returnstring = CONCAT(returnstring,'<li>',part,'</li>')
    END WHILE;
    RETURN returnstring;
END

这个例子将返回一个 HTML 列表，其中包含各个部分。（需要添加必需的变量长度）

- ohjay

Answer 10

我相信你的问题可以用其他更简单的方法解决。但是为了回答你的问题，您可以利用JSON函数来“拆分”一个字符串。

在您的特定情况下，字符串'7 - 31'和'31 - 7'可以通过组合函数CONCAT()和REPLACE()重格式化为有效的JSON字符串：

SELECT CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]') score

输出将如下所示：

["31","7"]

这是一个有效的JSON字符串。

下一步是使用JSON_VALUE()函数提取每个对手的得分。

SELECT JSON_VALUE(CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]'),'$[0]') oponent1, JSON_VALUE(CONCAT('["',REPLACE('31 - 7',' - ','","'),'"]'),'$[1]') oponent2

结果将如下所示：

+-----------+----------+
| oponent1  | oponent2 |
+-----------+----------+
|       31  |        7 |
+-----------+----------+

最后，您可以随心所欲地操作这些值。也许您希望重新排序它们，将最低分数放在第一位，以便得分始终是标准格式，而不是取决于主队可能是谁。

- dailabala