Convert separator string to multiple values ​​in mysql

Question

I have an obsolete mysql table that contains a client id and a list of items, the latter as a comma-delimited string. For instance. "xyz001", "foo,bar,baz". This is a legacy, and the user insists on being able to edit the comma delimited string.

Now they have a requirement for a report table with the above divided into separate rows, for example.

"xyz001", "foo"
"xyz001", "bar"
"xyz001", "baz"

Interrupting a string in substrings is easy to do, and I wrote a procedure for this, creating a separate table, but this requires triggers to handle deletions, updates, and inserts. This query is rarely required (for example, once a month), but it must be absolutely updated when it is executed, for example, the overhead of triggers is not justified, and the planned tasks for creating the table may not be timely enough.

Is there a way to write a function to return a table or set so that I can join an identifier with individual elements on request?

Answer 1

This is called row moving . Here is an example of how you could do this with specifications:

, , + 1. , 255, 256 , 0 255.

int_table:

+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+

, , . ( legacy_table client items .)

select 
  legacy_table.client, 
  substring(
    legacy_table.items, 
    int_table.i + 1, 
    if(
      locate(',', legacy_table.items, int_table.i + 1) = 0, 
      length(legacy_table.items) + 1, 
      locate(',', legacy_table.items, int_table.i + 1)
    ) - (int_table.i + 1)
  ) as item
from legacy_table, int_table
where legacy_table.client = 'xyz001'
  and int_table.i < length(legacy_table.items)
  and (
    (int_table.i = 0) 
    or (substring(legacy_table.items, int_table.i, 1) = ',')
  )

, , , , , .

Answer 2

Numbers Tally, :

Select Substring(T.List, N.Value, Locate(', ', T.List + ', ', N.Value) - N.Value)
From Numbers As N
    Cross Join MyTable As T
Where N.Value <= Len(T.List)
    And Substring(', ' + T.List, N.Value, 1) = ', '

Numbers :

Create Table Numbers( Value int not null primary key )

Answer 3

.

, .

, UNION :

http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/

, .. - :

(SELECT col1, SPLIT_STR(col2, ',', 1) c2 from tbl)
UNION (SELECT col1, SPLIT_STR(col2, ',', 2) c2 from tbl where col2 like '%,%')
UNION (SELECT col1, SPLIT_STR(col2, ',', 3) c2 from tbl where col2 like '%,%,%')
...

mysql, , , , .

, -?

Answer 4

SQL, . PHP script .

, . , .

<?php
$table = "ref_app";
$pri_column = "Repo";
$column = "Topics";
$newTable = "topics";

$conn = mysql_connect("localhost", "dev", "password");
mysql_select_db("esb_data");
if($conn==null){
    echo "Connection not made";
    exit;
}

$result = mysql_query("select ".$pri_column.", ".$column." from ".$table);
if(mysql_errno()){
    echo "<br>".mysql_error();
    exit;
}

$applications = array();
while($row = mysql_fetch_array($result)){
    echo "<br>".$row[$pri_column];
    $topics = explode(",", $row[$column]);
    foreach($topics as $topic){
        $topic = trim($topic);
        $applications[$row[$pri_column]][$topic] = $topic;
    }
    echo "<br>".$row[$column];
}
echo "<pre>";
print_r($applications);
echo "</pre>";

foreach($applications as $app => $topics){
    foreach($topics as $topic){
        $query = "insert into ".$newTable." values ('', \"".$app."\", \"".$topic."\")";
        echo "<br>".$query;
        mysql_query($query);
        if(mysql_errno()){
            echo "<br>".mysql_error();
        }
    }
}
?>

Answer 5

• , UNION

• MySQL 5.7

• xyz001epo

• ,

• , UNION .
• , (, , - , http:// - )

SELECT
  tta.*
FROM
(
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',2),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',3),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',4),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',5),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',6),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',7),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',8),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',9),'/',-1)  as txtvalu
    FROM zzdemo_uu112pingasrcdata
UNION
    SELECT
      rowid                                                              as txtname
      ,SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(fld005,'/'),'/',10),'/',-1) as txtvalu
    FROM zzdemo_uu112pingasrcdata
) as tta
WHERE 1
  AND (txtvalu is not null)
  AND (txtvalu <> '')
;;;

• , MySQL,
• , MySQL

• ,
• this solution requires an end separator at the end of the line with delimiters, otherwise it will not give the desired result
• this approach is not aesthetic, it will hurt some eyes and it will hurt some feelings