mysql - 插入同一行另一列修改的行列数据

Question

我有一个“leedle”数据库，其中有一个具有以下列结构的“wp_posts”表:

id | content | post_title | post_name

“post_name”列用于 url。 “post_title”列包含俄语文本。

我需要从“post_title”中获取信息并将其插入到“post_name”中。用于执行此操作的 ruby​​ 代码如下:

require 'mysql'
con = Mysql.new('localhost', 'root', 'pass', 'leedle') 
con.query('set names utf8')
con.query("UPDATE wp_posts SET post_name = post_title") 
con.close

这还不是全部，我还需要将“post_title”中的俄语文本插入音译为“post_name”，将“привет”变为“privet”。我怎样才能做到这一点？

我找到了一个用英语音译俄语文本的 mysql 函数:

DELIMITER $$

CREATE FUNCTION `cool_url` (original VARCHAR(512)) RETURNS VARCHAR(512)
BEGIN

  DECLARE translit VARCHAR(512) DEFAULT '';
  DECLARE len INT(3) DEFAULT 0;
  DECLARE pos INT(3) DEFAULT 1;
  DECLARE letter CHAR(2);

  SET original = TRIM(LOWER(original));
  SET len = CHAR_LENGTH(original);

  WHILE (pos <= len) DO
    SET letter = SUBSTRING(original, pos, 1);

    CASE TRUE
      WHEN letter = 'а' THEN SET letter = 'a';
      WHEN letter = 'б' THEN SET letter = 'b';
      WHEN letter = 'в' THEN SET letter = 'v';
      WHEN letter = 'г' THEN SET letter = 'g';
      WHEN letter = 'д' THEN SET letter = 'd';
      WHEN letter = 'е' THEN SET letter = 'e';
      WHEN letter = 'ё' THEN SET letter = 'e';
      WHEN letter = 'ж' THEN SET letter = 'z';
      WHEN letter = 'з' THEN SET letter = 'z';
      WHEN letter = 'и' THEN SET letter = 'i';
      WHEN letter = 'й' THEN SET letter = 'i';
      WHEN letter = 'к' THEN SET letter = 'k';
      WHEN letter = 'л' THEN SET letter = 'l';
      WHEN letter = 'м' THEN SET letter = 'm';
      WHEN letter = 'н' THEN SET letter = 'n';
      WHEN letter = 'о' THEN SET letter = 'o';
      WHEN letter = 'п' THEN SET letter = 'p';
      WHEN letter = 'р' THEN SET letter = 'r';
      WHEN letter = 'с' THEN SET letter = 's';
      WHEN letter = 'т' THEN SET letter = 't';
      WHEN letter = 'у' THEN SET letter = 'u';
      WHEN letter = 'ф' THEN SET letter = 'f';
      WHEN letter = 'х' THEN SET letter = 'ch';
      WHEN letter = 'ц' THEN SET letter = 'c';
      WHEN letter = 'ч' THEN SET letter = 'c';
      WHEN letter = 'ш' THEN SET letter = 's';
      WHEN letter = 'щ' THEN SET letter = 's';
      WHEN letter = 'ъ' THEN SET letter = '';
      WHEN letter = 'ы' THEN SET letter = 'y';
      WHEN letter = 'ь' THEN SET letter = 'i';
      WHEN letter = 'э' THEN SET letter = 'e';
      WHEN letter = 'ю' THEN SET letter = 'ju';
      WHEN letter = 'я' THEN SET letter = 'ja';

      WHEN letter IN ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
        THEN SET letter = letter;

      ELSE
    SET letter = '-';

    END CASE;

    SET translit = CONCAT(translit, letter);
    SET pos = pos + 1;
  END WHILE;

  WHILE (translit REGEXP '\-{2,}') DO
        SET translit = REPLACE(translit, '--', '-');
  END WHILE;

  RETURN TRIM(BOTH '-' FROM translit);

END

DELIMITER ; $$

但是当我在 mysql 中运行它时，它返回:

mysql> UPDATE wp_posts SET post_name = cool_url(post_title);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Answer 1

我建议用 ruby​​ 进行音译。为此，您需要加载俄语音译规则:

1. 创建一个包含俄语音译规则的文件(例如 /config/transliterations.yml)。看看我的gist
2. 需要“active_support/inflector”

3. 使用此代码:

   I18n.load_path = Dir['config/*.yml']
   I18n.backend.load_translations
   I18n.transliterate 'привет'