MySQL: сочетание кодировки в одном столбце

Question

Я использую OCS Inventory для инвентаризации наших компьютеров. Он работает почти нормально, но... но 99% этих ПК работают под Windows(R): XP и Seven. И нет никакого стандарта для информации WMI.

Например, агент OCS (резидентная программа на целевом ПК, которая собирает данные и отправляет их на сервер OCS) может получить ответ как "Контроллер семейства Realtek PCIe GBE (1 Гбит / с)" (чистый английский, кодирование не является проблемой), или как "¨ £ ¡¨ ®¥ ®¥ ¥ ¥ ¥¢®¥ ¯®" îç¥ ¨¥ Intel(R) 82566DM-2 (100 Мбит / с)"(неверно, русский, CP1251) или как "Realtek RTL8169 / 8110 Семейство сетевых адаптеров Gigabit Ethernet - универсальный сетевой адаптер (100 Мбит / с)" (неверно, русский, CP866). Или "VIA Rhine III Fast Ethernet Adapter - Минипорт планировщика пакетов (100 Мбит / с)" (правильный, русский, UTF8 или другой Unicode)

У меня нет никакого способа заставить Windows возвращать эти результаты только в одной кодировке: кажется, это зависит от производителей драйверов: какая кодировка использовалась в файле.INF драйвера, эта кодировка будет возвращена WMI.

В основном это не проблема, но моим руководителям очень не нравится видеть "иероглифы" в ежеквартальных отчетах о состоянии нашей ИТ-инфраструктуры. И они, кажется, правы.

Есть ли способ перекодировать отдельные поля из одной кодировки в другую на лету? Я не буду перекодировать весь столбец из-за смешанного содержимого. Я могу приблизительно определить, какая кодировка использовалась, но я не знаю, как перекодировать, используя только язык SQL MySQL для получения правильных отчетов.

Любые рабочие предложения, пожалуйста?

Answer 1

Похоже, единственным способом было написание хранимой процедуры. Я сделал это. Левый столбец (castbin) текст перед обработкой, верно (converted) тот же текст после обработки

Давай посмотрим код

...
#above and below are regular MySQL statements
@castbin:=cast(networks.description as char character set binary) as castbin,
@convv:=convert(repcxaxex(@castbin) using cp866) as converted
...

@castbin Переменная используется только для уточнения и читабельности. Вся работа сделана repCxAxEx функция, названная в честь "заменить 0xCx 0xAx на 0xCx 0xEx". Вот очень неоптимальный, но рабочий код этой функции (взят из MySQL Studio)

See corrected text of the function below in update section

Сначала мы смотрим, является ли строка конвертируемой, затем делаем два преобразования - для 0xC2 а также 0xC3 префиксы, потому что эти префиксы означают разные ошибки кодирования.

ОБНОВЛЕНИЕ: Тщательное тестирование выявило некоторые ошибки.

Где-то внутри запроса я использую group_concat оператор для объединения значений нескольких свойств одного и того же ПК в одну длинную строку с "\n" в качестве разделителя. В таких случаях преобразование может не работать.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `repcxaxex`(s2c text charset binary) RETURNS blob
begin
/*исправляем проблемы с кодировками*/
    set @i:=0;
    set @s:=s2c;
    set @altered:='0';
    if ((left(@s,1)=char(0xc2)) or instr(@s,char(32,0xc2)) or instr(@s,char(0x0a,0xc2))) then
    while @i<16 do
        set @s:=replace(@s,char(0xc3,(160+@i)),char(0xd3,(224+@i)));
        set @i:=@i+1;
    end while;
    set @i:=1;
    while @i<4 do
        set @s:=replace(@s,char(0xc0+@i),'');
        set @s:=replace(@s,char(0xd0+@i),'');
        set @i:=@i+1;
    end while;
    set @altered:='1';
    end if;
    set @i:=0;
    if ((left(@s,1)=char(0xc3)) or instr(@s,char(32,0xc3)) or instr(@s,char(0x0a,0xc3))) then
    while @i<16 do
        set @s:=replace(@s,char(0xc3,(176+@i)),char(0xd3,(224+@i)));
        set @i:=@i+1;
    end while;
    set @i:=1;
    while @i<4 do
        set @s:=replace(@s,char(0xc0+@i),'');
        set @s:=replace(@s,char(0xd0+@i),'');
        set @i:=@i+1;
    end while;
    set @altered:='2';
    end if;
/*Добавляем 0 или 1 в начало строки, чтобы показать, конвертировали ее или нет
выводить надо будет, начиная со второго символа*/
    set @s=concat(@altered,@s);
    return @s;
end

При выходе функция добавляет одну цифру в начало возвращаемой строки. Сама цифра определяется @altered переменная, и ее код говорит сам за себя.

Более правильная форма вызова:

If (left(repcxaxex(string-to-convert),1)='0',string-to-convert,mid(convert(repcxaxex(string-to-convert) using cp866),2))