迹忆客 专注技术分享

MySQL 将数据存储在表的列和行中，用户可以定义、控制、操作和查询这些数据。 MySQL 为我们提供了各种功能，我们可以拥有的一个功能是将字符串拆分为行。

本文讨论将字符串拆分为行、它的用途以及如何在 MySQL 中执行它。

在 MySQL 中将字符串拆分为行

当我们操作数据库中的数据时，有时我们会与字符串进行交互。 例如，一个表可以包含客户的全名、他们的地址和一些描述。

有时，我们将这些数据添加为字符串，出于各种目的，我们可能需要将它们分成行。 为了实现这个目标，MySQL 为我们提供了一个方法，SUBSTRING_INDEX()。

使用 SUBSTRING_INDEX() 方法将字符串拆分为行

SUBSTRING_INDEX() 是 MySQL 提供给我们的一个特性，可以从一个字符串中派生出一个子串。 它检查特定的分隔符，因此将输出它之前的子字符串。

语法如下：

SUBSTRING_INDEX(string, delimiter, number);

在上面的语法中，有三个参数。 字符串是指我们输入的行来导出子串，分隔符是函数要搜索的值。

分隔符将被搜索的次数称为次数。 该数字可以是正数或负数。

如果是正数，我们会从字符串的前面到后面得到分隔符左边的子串。 如果是负数，我们就取分隔符右边的子串，从字符串的后部到前部开始搜索分隔符。

让我们举个例子。

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',2) AS newp;

如上所示，我们给出了一个包含四个以逗号分隔的国家/地区的字符串。 然后作为分隔符，逗号过去了，作为数字，我们分配了两个。

让我们看看结果。

输出：

正如您所看到的，我们得到了第二个逗号左边的子字符串，因为我们已经给出了 2 作为数字。 让我们用一个负数作为数字再试一次。

SELECT SUBSTRING_INDEX('England, America, Japan, China',',',-1) AS newp;

下面是上面代码的结果，正如你所看到的，我们得到了中国作为输出，因为我们给了 -1 作为数字，因为它是一个负数，它搜索了最后一个分隔符并输出了正确的子字符串 给它。

输出：

现在，让我们看看如何使用此方法将字符串拆分为行。 请参考以下示例。

DELIMITER //

-- Creating a procedure
CREATE procedure newProcedure()

BEGIN
-- Declaring a variable as myvar to store the string
DECLARE myvar varchar (300);

-- Assigning the string to the variable
SET myvar = 'China.Japan.USA';

-- Deriving the substrings
(SELECT SUBSTRING_INDEX(myvar, '.', 1) AS Countries)
UNION
(SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(myvar, '.', 2),'.','-1') AS Countries)
UNION
(SELECT SUBSTRING_INDEX(myvar, '.', -1) AS Countries);
END //
DELIMITER ;

-- Calling the procedure
CALL newProcedure()

在上面的代码中，我们创建了一个名为 newProcedure 的过程，然后声明了一个变量来存储字符串。 该字符串包含句号，因此它将成为我们的分隔符。

我们已经使用了 SUBSTING_INDEX 方法四次（在中间两次以获取中间的子字符串），并使用 UNION 将这些子字符串放在一个列中。 最后，我们调用了我们的程序，输出如下。

输出：

我们已将字符串分成多行，但是当我们有三个以上的句号时，这段代码将具有挑战性。

为了克服这个问题，我们可以构建一个解决方案。 首先，我们可以创建一个包含国家/地区名称的单列表。

我们添加的国家名称有多个名称，因为我们需要拆分全名。 让我们创建一个包含三个国家名称的表，如下所示。

CREATE TABLE countries (countryName VARCHAR(100));
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

现在让我们创建另一个名为 numberList 的表，其中包含一、二和三作为数据。

CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

该表的目的是设置要拆分的最大子串数。 我们添加了三个数字，当代码执行时，它会将字符串分成最多三个子字符串，即使行内有四个子字符串也是如此。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

在上面的代码块中，我们使用了 SUBSTRING_INDEX 函数，并且在其中，我们将其作为分隔符进行搜索，因为国家名称是用空格分隔的。 然后我们检查国家名称是否有三个以上的子串。

之后，我们按行打印子字符串。

完整代码：

-- Creating the table
CREATE TABLE countries (countryName VARCHAR(100));

-- Inserting values
INSERT INTO countries VALUES
('United States America'),
('New Zeland'),
('United Kingdom');

-- Create a table that contains the numbers
CREATE TABLE numberList (indexing INT);
INSERT INTO numberList VALUES (1),(2),(3);

-- Deriving the substrings
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(countries.countryName, ' ', numberList.indexing), ' ', -1) countryName
FROM numberList
INNER JOIN countries
ON CHAR_LENGTH (countries.countryName) -CHAR_LENGTH(REPLACE(countries.countryName, ' ', '')) >= numberList.indexing - 1

下面是我们运行代码后得到的结果。

输出：

如您所见，正如我们所料，国家/地区名称被分成子字符串。

总结

通过这篇文章，我们对MySQL有了一个简单的介绍。 然后我们了解了为什么我们需要将字符串拆分为行以及如何在 MySQL 中执行它。

为了实现这个目标，MySQL 为我们提供了 SUBSTRING_INDEX 方法，我们看看如何使用它来完成任务。

还有其他方法可以完成此任务，例如 STRING_SPLIT 函数，但我们在本文中讨论的技术是 MySQL 中的最佳方法。