mysql - Penggantian rentetan berdasarkan kamus

Question

Jadual sumber: Jadual kamus: Ia dijangka menggantikan nilai dalam jadual sumber mengikut jadual kamus Hasil sasaran adalah seperti berikut: Saya cuba menggunakan fungsi ganti, tetapi ia hanya boleh diganti secara individu. Nota: Memandangkan nilai lain dipisahkan dengan koma, id jadual kamus boleh 1~100 , perlu digantikan dengan ungkapan biasa, jika tidak 12 akan dipisahkan dengan id1,2)

给我你的怀抱 · Answer

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.