MySQL如何实现西里尔文与拉丁文等效比较？塞尔维亚双语场景

开发者阿泽

2026-5-26

Great question! Unfortunately, MySQL doesn’t have a built-in collation that handles transliteration equivalence between Cyrillic and Latin (like matching "Дог" to "Dog" and vice versa). Collations are designed for character-level equivalence (e.g., case insensitivity, accent folding), not phonetic or script-transliterated matches. But there’s a reliable workaround using custom functions and preprocessed data that fits perfectly for Serbia’s dual-script system. Here’s how to implement it:

Solution Overview

We’ll create a custom function to transliterate Cyrillic text to its standard Latin equivalent (per Serbian language rules), then store this normalized string in a dedicated column. When querying, we’ll convert user input (whether Cyrillic or Latin) to the same normalized format and match against this column. This ensures fast, consistent matches regardless of the input script.

Step 1: Create a Cyrillic-to-Latin Transliteration Function

First, we’ll build a function that maps every Serbian Cyrillic character to its corresponding Latin counterpart. This covers all special characters unique to Serbian (like Ђ, Љ, Њ) too:

DELIMITER //
CREATE FUNCTION cyrillic_to_latin(input_str VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE output_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE current_char CHAR(1);
    
    WHILE i <= LENGTH(input_str) DO
        SET current_char = SUBSTRING(input_str, i, 1);
        CASE current_char
            -- Uppercase Cyrillic to Latin
            WHEN 'А' THEN SET output_str = CONCAT(output_str, 'A');
            WHEN 'Б' THEN SET output_str = CONCAT(output_str, 'B');
            WHEN 'В' THEN SET output_str = CONCAT(output_str, 'V');
            WHEN 'Г' THEN SET output_str = CONCAT(output_str, 'G');
            WHEN 'Д' THEN SET output_str = CONCAT(output_str, 'D');
            WHEN 'Ђ' THEN SET output_str = CONCAT(output_str, 'Đ');
            WHEN 'Е' THEN SET output_str = CONCAT(output_str, 'E');
            WHEN 'Ж' THEN SET output_str = CONCAT(output_str, 'Ž');
            WHEN 'З' THEN SET output_str = CONCAT(output_str, 'Z');
            WHEN 'И' THEN SET output_str = CONCAT(output_str, 'I');
            WHEN 'Ј' THEN SET output_str = CONCAT(output_str, 'J');
            WHEN 'К' THEN SET output_str = CONCAT(output_str, 'K');
            WHEN 'Л' THEN SET output_str = CONCAT(output_str, 'L');
            WHEN 'Љ' THEN SET output_str = CONCAT(output_str, 'Lj');
            WHEN 'М' THEN SET output_str = CONCAT(output_str, 'M');
            WHEN 'Н' THEN SET output_str = CONCAT(output_str, 'N');
            WHEN 'Њ' THEN SET output_str = CONCAT(output_str, 'Nj');
            WHEN 'О' THEN SET output_str = CONCAT(output_str, 'O');
            WHEN 'П' THEN SET output_str = CONCAT(output_str, 'P');
            WHEN 'Р' THEN SET output_str = CONCAT(output_str, 'R');
            WHEN 'С' THEN SET output_str = CONCAT(output_str, 'S');
            WHEN 'Т' THEN SET output_str = CONCAT(output_str, 'T');
            WHEN 'Ћ' THEN SET output_str = CONCAT(output_str, 'Ć');
            WHEN 'У' THEN SET output_str = CONCAT(output_str, 'U');
            WHEN 'Ф' THEN SET output_str = CONCAT(output_str, 'F');
            WHEN 'Х' THEN SET output_str = CONCAT(output_str, 'H');
            WHEN 'Ц' THEN SET output_str = CONCAT(output_str, 'C');
            WHEN 'Ч' THEN SET output_str = CONCAT(output_str, 'Č');
            WHEN 'Џ' THEN SET output_str = CONCAT(output_str, 'Dž');
            WHEN 'Ш' THEN SET output_str = CONCAT(output_str, 'Š');
            -- Lowercase Cyrillic to Latin
            WHEN 'а' THEN SET output_str = CONCAT(output_str, 'a');
            WHEN 'б' THEN SET output_str = CONCAT(output_str, 'b');
            WHEN 'в' THEN SET output_str = CONCAT(output_str, 'v');
            WHEN 'г' THEN SET output_str = CONCAT(output_str, 'g');
            WHEN 'д' THEN SET output_str = CONCAT(output_str, 'd');
            WHEN 'ђ' THEN SET output_str = CONCAT(output_str, 'đ');
            WHEN 'е' THEN SET output_str = CONCAT(output_str, 'e');
            WHEN 'ж' THEN SET output_str = CONCAT(output_str, 'ž');
            WHEN 'з' THEN SET output_str = CONCAT(output_str, 'z');
            WHEN 'и' THEN SET output_str = CONCAT(output_str, 'i');
            WHEN 'ј' THEN SET output_str = CONCAT(output_str, 'j');
            WHEN 'к' THEN SET output_str = CONCAT(output_str, 'k');
            WHEN 'л' THEN SET output_str = CONCAT(output_str, 'l');
            WHEN 'љ' THEN SET output_str = CONCAT(output_str, 'lj');
            WHEN 'м' THEN SET output_str = CONCAT(output_str, 'm');
            WHEN 'н' THEN SET output_str = CONCAT(output_str, 'n');
            WHEN 'њ' THEN SET output_str = CONCAT(output_str, 'nj');
            WHEN 'о' THEN SET output_str = CONCAT(output_str, 'o');
            WHEN 'п' THEN SET output_str = CONCAT(output_str, 'p');
            WHEN 'р' THEN SET output_str = CONCAT(output_str, 'r');
            WHEN 'с' THEN SET output_str = CONCAT(output_str, 's');
            WHEN 'т' THEN SET output_str = CONCAT(output_str, 't');
            WHEN 'ћ' THEN SET output_str = CONCAT(output_str, 'ć');
            WHEN 'у' THEN SET output_str = CONCAT(output_str, 'u');
            WHEN 'ф' THEN SET output_str = CONCAT(output_str, 'f');
            WHEN 'х' THEN SET output_str = CONCAT(output_str, 'h');
            WHEN 'ц' THEN SET output_str = CONCAT(output_str, 'c');
            WHEN 'ч' THEN SET output_str = CONCAT(output_str, 'č');
            WHEN 'џ' THEN SET output_str = CONCAT(output_str, 'dž');
            WHEN 'ш' THEN SET output_str = CONCAT(output_str, 'š');
            -- Keep non-Cyrillic characters (spaces, punctuation, Latin text) as-is
            ELSE SET output_str = CONCAT(output_str, current_char);
        END CASE;
        SET i = i + 1;
    END WHILE;
    -- Optional: Convert to lowercase for case-insensitive matching
    RETURN LOWER(output_str);
END //
DELIMITER ;

Step 2: Add a Normalized Search Column

Add a dedicated column to your table to store the transliterated/normalized string. This lets us use indexes for fast queries:

ALTER TABLE your_table_name ADD COLUMN search_normalized VARCHAR(255) AFTER your_text_column;

Replace your_table_name and your_text_column with your actual table and column names (e.g., products and product_name).

Step 3: Populate and Automate the Search Column

First, update existing rows to fill the new column:

UPDATE your_table_name SET search_normalized = cyrillic_to_latin(your_text_column);

Then create triggers to automatically populate this column when inserting or updating data, so you don’t have to manually run the function every time:

-- Trigger for inserts
DELIMITER //
CREATE TRIGGER before_insert_normalize
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.search_normalized = cyrillic_to_latin(NEW.your_text_column);
END //
DELIMITER ;

-- Trigger for updates
DELIMITER //
CREATE TRIGGER before_update_normalize
BEFORE UPDATE ON your_table_name
FOR EACH ROW
BEGIN
    SET NEW.search_normalized = cyrillic_to_latin(NEW.your_text_column);
END //
DELIMITER ;

Step 4: Query for Transliterated Matches

When a user inputs text (whether Cyrillic or Latin), convert it using the same function and match against the normalized column:

If user inputs Cyrillic "Дог":
```
SELECT * FROM your_table_name 
WHERE search_normalized = cyrillic_to_latin('Дог');
```
This returns rows where the original text is either "Дог" or "Dog".
If user inputs Latin "Dog":
```
SELECT * FROM your_table_name 
WHERE search_normalized = cyrillic_to_latin('Dog');
```
Since the function leaves Latin text unchanged, this also returns both "Дог" and "Dog" rows.

For better performance, add an index to the search_normalized column:

CREATE INDEX idx_search_normalized ON your_table_name(search_normalized);

Key Notes

No Built-in Collation: As you noticed, MySQL’s collations don’t handle transliteration — they only handle character equivalence (e.g., é = e in utf8mb4_unicode_ci). This workaround is the standard approach for script-transliterated search.
Serbian-Specific Mapping: The function above follows official Serbian transliteration rules, including special characters like Ђ → Đ and Љ → Lj. Adjust the case statements if you need to support other regional variations.
Case Insensitivity: The function returns lowercase text, so queries are case-insensitive by default. Remove the LOWER() call if you need case-sensitive matches.

内容的提问来源于stack exchange，提问作者Alex Fedorin