MySQL: кодировка символов, используемая SELECT INTO?

Я пытаюсь экспортировать некоторые данные из базы данных MySQL, но странные и замечательные вещи происходят с unicode в этой таблице.

Я сосредоточусь на одном персонаже, левом smartquote: "

Когда я использую SELECT с консоли, он печатается без проблем:

mysql> SELECT text FROM posts;
+-------+
| text  |
+-------+
| "foo" |
+-------+

Это означает, что данные отправляются на мой терминал как utf-8 [0] (что правильно).

Однако, когда я использую SELECT * FROM posts INTO OUTFILE '/tmp/x.csv' …;, выходной файл неправильно закодирован:

$ cat /tmp/x.csv
“fooâ€

В частности, " закодирован с семью (7!) байтами: \xc3\xa2\xe2\x82\xac\xc5\x93.

Что такое кодировка? Или как я могу сказать MySQL использовать менее необоснованную кодировку?

Кроме того, некоторые разные факты:

  • SELECT @@character_set_database возвращает latin1
  • Столбец text имеет значение VARCHAR(42): 
    
mysql> DESCRIBE posts;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| text  | varchar(42) | NO   | MUL |         |       |
+-------+-------------+------+-----+---------+-------+
  • ", закодированный как utf-8, дает \xe2\x80\x9c
  • \xe2\x80\x9c декодируется как latin1, а затем перекодируется как utf-8 дает \xc3\xa2\xc2\x80\xc2\x9c (6 байтов).
  • Другая точка данных: … (utf-8: \xe2\x80\xa6) закодирована до \xc3\xa2\xe2\x82\xac\xc2\xa6

[0]: поскольку интеллектуальные кавычки не включены ни в одну 8-битную кодировку, а мой терминал правильно отображает символы utf-8.

Ответы

Ответ 1

Многие программы/стандарты (включая MySQL) предполагают, что "latin1" означает "cp1252", поэтому байт 0x80 интерпретируется как символ евро, где этот бит \xe2\x82\xac (U + 20AC) происходит из середины.

Когда я пытаюсь это сделать, он работает правильно (но обратите внимание, как я вставлял данные и переменные, установленные на сервере db):

mysql> set names utf8; -- http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html
mysql> create table sq (c varchar(10)) character set utf8;
mysql> show create table sq\G
*************************** 1. row ***************************
       Table: sq
Create Table: CREATE TABLE `sq` (
  `c` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.19 sec)

mysql> insert into sq values (unhex('E2809C'));
Query OK, 1 row affected (0.00 sec)

mysql> select hex(c), c from sq;
+--------+------+
| hex(c) | c    |
+--------+------+
| E2809C | "  |
+--------+------+
1 row in set (0.00 sec)

mysql> select * from sq into outfile '/tmp/x.csv';
Query OK, 1 row affected (0.02 sec)

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       | 
| character_set_connection | utf8                       | 
| character_set_database   | utf8                       | 
| character_set_filesystem | binary                     | 
| character_set_results    | utf8                       | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

И из оболочки:

/tmp$ hexdump -C x.csv
00000000  e2 80 9c 0a                                       |....|
00000004

Надеюсь, там будет полезный лакомый кусочек...

Ответ 2

Более новые версии MySQL имеют возможность установить набор символов в предложении outfile:

SELECT col1,col2,col3 
FROM table1 
INTO OUTFILE '/tmp/out.txt' 
CHARACTER SET utf8
FIELDS TERMINATED BY ','

Ответ 3

Чтобы конкретно рассмотреть ваш вопрос "Что это?", вы сами ответили на него:

Я подозреваю, что это происходит потому, что "Значения столбцов выгружаются с использованием двоичного набора символов. По сути, преобразование набора символов не существует". - dev.mysql.com/doc/refman/5.0/en/select-into.html

Таким образом, MySQL хранит utf8 закодированные данные внутренне. Это ужасно неэффективное изменение хранилища Unicode, по-видимому, с использованием трех байтов для большинства символов и не поддерживающих четыре байтовых последовательности UTF-8.

Как преобразовать его в реальный UTF-8 с помощью INTO OUTFILE... Я не знаю. Использование других методов mysqldump сделает это, хотя.

Ответ 4

Как вы можете видеть, моя база данных MySQL использует latin1, а система - utf-8.

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+
7 rows in set (0.00 sec)

Каждый раз, когда я пытался экспортировать таблицу, я получил странный кодированный CSV файл. Итак, я положил:

mysql_query("SET NAMES CP1252");
header('Content-Type: text/csv; charset=cp1252');
header('Content-Disposition: attachment;filename=output.csv');

как в export script.

Затем у меня есть чистый вывод UTF-8.

Ответ 5

Я обнаружил, что это хорошо работает.

SELECT convert(col_name USING latin1) FROM posts INTO OUTFILE '/tmp/x.csv' …;

Ответ 7

Вы можете выполнять MySQL-запросы с помощью инструмента CLI (я считаю, даже с выходным форматом, чтобы он печатал CSV) и перенаправлял файл. Необходимо преобразовать кодировку и по-прежнему предоставлять вам доступ к объединениям и т.д.

Ответ 8

Вам нужно выпустить charset utf8 в приглашении MySQL перед запуском SELECT. Это сообщает серверу, что выводить результаты как.