MySQL字符數據查詢拆分

問題描述

數據表中某字段為特定單詞組字符串，特定字符分隔。
現有需求：在不影響原始數據的情況下，查詢顯示拆分后的單詞，方便后續對其進行后續操作。

演示

演示數據源

    -- 測試表結構create table word_table(words varchar(255) null);-- 表數據INSERT INTO test.word_table(words) VALUES ('apple,orange,lemon');INSERT INTO test.word_table(words) VALUES ('computer,apple');INSERT INTO test.word_table(words) VALUES ('laddy,man,woman');

涉及MySQL內置表/內置函數

MySQL內置表

• mysql.help_topic

  mysql.help_topic本身是Mysql的一張信息表,用來存儲各種注釋等幫助信息。這里需要借助該表中的help_topic_id字段數據，其有特點：help_topic_id 從0開始，固定數量的（和數據庫版本有關），我這里為0~699。

      -- 查看help_topic_id序列總數SELECT help_topic_id FROM mysql.help_topic ORDER BY help_topic_id ASC
  

  -- 測試
  SELECT help_topic_id FROM mysql.help_topic WHERE help_topic_id<2
  

  +---------------+
  | help_topic_id |
  +---------------+
  |             0 |
  |             1 |
  +---------------+
  2 rows in set (0.00 sec)
  

內置函數

• LENGTH(str)

  返回str中的字符個數

• REPLACE(str, old_sub_str, new_sub_str)

  替換str中 舊的子字串 為 新的子字符串

• SUBSTRING_INDEX(str, sub_str, count)

  返回 str 中 第 count 次 查找到 sub_str 之前的字符串，不包含最后一次查找到的sub_str。

  如：SUBSTRING_INDEX("a,b,a,c,a,d",'a',3) ==> "a,b,a,c,"

  注意：如果count為負數，則表示從右邊查找，并返回右邊的字符串。

  mysql> -- 拆分單詞
  mysql> -- SUBSTRING_INDEX(str, sub_str, count):在str中查找delim，返回查找到的第count個sub_str之前的字符串(從左開始)
  mysql> -- 如果count為負數，則從右開始
  mysql> -- 示例：取左邊第一個單詞
  mysql> SELECT SUBSTRING_INDEX(T.words,',',1) FROM word_table T ;
  +--------------------------------+
  | SUBSTRING_INDEX(T.words,',',1) |
  +--------------------------------+
  | apple                          |
  | computer                       |
  | laddy                          |
  +--------------------------------+
  3 rows in set (0.00 sec)mysql> -- 示例：取左邊二個單詞
  mysql> SELECT SUBSTRING_INDEX(T.words,',',2) FROM word_table T ;
  +--------------------------------+
  | SUBSTRING_INDEX(T.words,',',2) |
  +--------------------------------+
  | apple,orange                   |
  | computer,apple                 |
  | laddy,man                      |
  +--------------------------------+
  3 rows in set (0.00 sec)mysql> -- 示例：取右邊一個單詞,由此可以把每一個單詞都拿出來
  mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',1),',',-1) FROM word_table T ;
  +--------------------------------------------------------+
  | SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',1),',',-1) |
  +--------------------------------------------------------+
  | apple                                                  |
  | computer                                               |
  | laddy                                                  |
  +--------------------------------------------------------+
  3 rows in set (0.00 sec)mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',2),',',-1) FROM word_table T ;
  +--------------------------------------------------------+
  | SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',2),',',-1) |
  +--------------------------------------------------------+
  | orange                                                 |
  | apple                                                  |
  | man                                                    |
  +--------------------------------------------------------+
  3 rows in set (0.00 sec)
  

基本實現思路

1. 計算單詞組字符串中的分隔符個數從而得出單詞個數(分隔符個數+1)。

   	-- 計算單詞個數，即分隔符的個數+1，便為單詞個數-- LENGTH:計算字符串中的字符個數-- REPLACE： 分換字符串中的字符，這里是將分隔符刪除SELECT words,(LENGTH(T.words) - LENGTH(REPLACE(T.words, ',',''))+1) AS wordsCount FROM word_table T
   

   +--------------------+------------+
   | words              | wordsCount |
   +--------------------+------------+
   | apple,orange,lemon |          3 |
   | computer,apple     |          2 |
   | laddy,man,woman    |          3 |
   +--------------------+------------+
   3 rows in set (0.00 sec)
   

2. 根據單詞個數顯示查詢行數。

   	-- 借助mysql.help_topic表中的help_topic_id 進行關聯，進行拆分第一步-- help_topic_id 從0開始，固定數量的（和數據庫版本有關），我這里為0~699-- 示例：help_topic_id < 2，則符合條件的有0,1SELECT T.words FROM word_table T INNER JOIN mysql.help_topic H ON H.help_topic_id < (LENGTH(T.words) - LENGTH(REPLACE(T.words, ',',''))+1)
   

   +--------------------+
   | words              |
   +--------------------+
   | apple,orange,lemon |
   | apple,orange,lemon |
   | apple,orange,lemon |
   | computer,apple     |
   | computer,apple     |
   | laddy,man,woman    |
   | laddy,man,woman    |
   | laddy,man,woman    |
   +--------------------+
   8 rows in set (0.00 sec)
   

3. 在該組的每一行，根據順序依次切割出單詞。

   第一次切割出一個，第二次切割出兩個，第三次切割出三個。

   	-- 通過 mysql.help_topic 的help_topic_id 序列ID來順序分割SELECT SUBSTRING_INDEX(T.words,',',H.help_topic_id+1) FROM word_table T INNER JOIN mysql.help_topic HON H.help_topic_id < (LENGTH(T.words)-LENGTH(REPLACE(T.words,',',''))+1)
   

   +------------------------------------------------+
   | SUBSTRING_INDEX(T.words,',',H.help_topic_id+1) |
   +------------------------------------------------+
   | apple                                          |
   | apple,orange                                   |
   | apple,orange,lemon                             |
   | computer                                       |
   | computer,apple                                 |
   | laddy                                          |
   | laddy,man                                      |
   | laddy,man,woman                                |
   +------------------------------------------------+
   8 rows in set (0.00 sec)
   

4. 每行拿右邊第一個單詞即可。

   	-- 完整拆分操作SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',H.help_topic_id+1),',',-1) FROM word_table T INNER JOIN mysql.help_topic HON H.help_topic_id < (LENGTH(T.words)-LENGTH(REPLACE(T.words,',',''))+1)
   

   +------------------------------------------------------------------------+
   | SUBSTRING_INDEX(SUBSTRING_INDEX(T.words,',',H.help_topic_id+1),',',-1) |
   +------------------------------------------------------------------------+
   | apple                                                                  |
   | orange                                                                 |
   | lemon                                                                  |
   | computer                                                               |
   | apple                                                                  |
   | laddy                                                                  |
   | man                                                                    |
   | woman                                                                  |
   +------------------------------------------------------------------------+
   8 rows in set (0.00 sec)
   

   參考博文

   https://www.jb51.net/database/305918jub.htm

   https://blog.csdn.net/qq_23845083/article/details/135410361

   https://www.lidihuo.com/mysql/mysql-string-substring_index-function.html