mysql - Penggantian rentetan berdasarkan kamus

Question

Jadual sumber:

Jadual kamus:

Dijangkakan bahawa nilai dalam jadual sumber akan diganti mengikut jadual kamus Hasil sasaran adalah seperti berikut:

.

Saya cuba menggunakan fungsi ganti, tetapi ia hanya boleh menggantikan satu sahaja
(Nota: Memandangkan yang lain dipisahkan dengan koma, id jadual kamus boleh menjadi 1~100, jadi ia perlu diganti dengan ungkapan biasa. jika tidak 12 akan dipecah oleh id1 dan 2)

Answer 1

Fungsi rekursif ditulis oleh saya sendiri

DELIMITER $$
DROP FUNCTION IF EXISTS junk.StringReplaceSplit $$
CREATE FUNCTION junk.StringReplaceSplit (v_str VARCHAR(1024), v_split VARCHAR(1024)) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
  DECLARE rv VARCHAR(1024);
  DECLARE splist_count INT;
  DECLARE i INT;
  DROP TEMPORARY TABLE IF EXISTS tmp_table;
  CREATE TEMPORARY TABLE tmp_table(v_key VARCHAR(100));
 
  SET i = 1;
  set splist_count = (length(v_str)-length(replace(v_str, v_split,'')))/length(v_split)+1;
  WHILE i <= splist_count DO
    INSERT INTO tmp_table
      SELECT REPLACE(substring(substring_index(v_str, v_split, i),
                     length(substring_index(v_str, v_split, i-1)) + 1),
                      v_split, '');
    SET i = i + 1;
  END WHILE;
  SELECT replace(group_concat(ifnull(wordlist.word, tmp_table.v_key)), ',', v_split) INTO rv
    from tmp_table
    LEFT JOIN wordlist on wordlist.id=tmp_table.v_key;
  RETURN rv;
END $$
DELIMITER ;

Kesan:
Jadual sumber

mysql> select * from teststring;
+----+----------------------------+
| id | String                     |
+----+----------------------------+
|  1 | 1,2,3,4,5                  |
|  2 | 4,5,7,8,84                 |
|  3 | key,23,344,12,3,9,34,3,1,3 |
+----+----------------------------+
3 rows in set (0.00 sec)

Meja kamus

mysql> select * from wordlist;
+----+-------+
| id | word  |
+----+-------+
| 1  | one   |
| 2  | two   |
| 3  | three |
| 4  | four  |
| 5  | five  |
| 7  | six   |
| 8  | eight |
| 9  | nine  |
| 10 | ten   |
+----+-------+
9 rows in set (0.00 sec)

Contoh:

mysql> SELECT id, StringReplaceSplit(String,',') transform from teststring ;
+----+---------------------------------------------+
| id | transform                                   |
+----+---------------------------------------------+
|  1 | one,two,three,four,five                     |
|  2 | four,five,six,eight,84                      |
|  3 | key,23,344,12,three,nine,34,three,one,three |
+----+---------------------------------------------+
3 rows in set (0.04 sec)

Fungsi perlu dioptimumkan.