mysql - outfile - select into sql

MySQL: codificación de caracteres utilizada por SELECT INTO?

Question

Estoy tratando de exportar algunos datos de una base de datos MySQL, pero en esa tabla ocurren cosas extrañas y maravillosas.

Me centraré en un personaje, el smartquote de la izquierda: "

Cuando uso SELECT desde la consola, se imprime sin problemas:

mysql> SELECT text FROM posts;
+-------+
| text  |
+-------+
| “foo” |
+-------+

Esto significa que los datos se están enviando a mi terminal como utf-8 [0] (lo que es correcto).

Sin embargo, cuando uso SELECT * FROM posts INTO OUTFILE ''/tmp/x.csv'' …; , el archivo de salida no está codificado correctamente:

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

Específicamente, el “ está codificado con siete (7!) Bytes: /xc3/xa2/xe2/x82/xac/xc5/x93 .

¿Qué codificación es esta? ¿O cómo podría decirle a MySQL que use una codificación menos irrazonable?

Además, algunos hechos misceláneos:

• SELECT @@character_set_database devuelve latin1
• La columna de text es un VARCHAR(42) :

  mysql> DESCRIBE posts; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | text | varchar(42) | NO | MUL | | | +-------+-------------+------+-----+---------+-------+

• “ /xe2/x80/x9c como utf-8 produce /xe2/x80/x9c
• /xe2/x80/x9c descodificado como latin1 luego recodificado como utf-8 produce /xc3/xa2/xc2/x80/xc2/x9c (6 bytes).
• Otro punto de datos: … (utf-8: /xe2/x80/xa6 ) está codificado en /xc3/xa2/xe2/x82/xac/xc2/xa6

[0]: como las comillas inteligentes no se incluyen en ninguna codificación de 8 bits, y mi terminal reproduce correctamente los caracteres utf-8.

Answer 1

Como puede ver, mi base de datos MySQL usa latin1 y el sistema es 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)

Cada vez que intentaba exportar tabla obtenía un archivo CSV codificado extraño. Entonces, puse:

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

como en mi script de exportación .

Entonces tengo salida pura de UTF-8.

Answer 2

He encontrado que esto funciona bien.

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

Answer 3

Las versiones más recientes de MySQL tienen una opción para establecer el conjunto de caracteres en la cláusula de salida:

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

Answer 4

Muchos programas / estándares (incluido MySQL) asumen que "latin1" significa "cp1252", por lo que el byte 0x80 se interpreta como un símbolo del euro, que es de donde /xe2/x82/xac bit /xe2/x82/xac (U + 20AC) en el medio .

Cuando intento esto, funciona correctamente (pero tenga en cuenta cómo coloco los datos y las variables configuradas en el servidor 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)

Y desde la cáscara:

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

Esperemos que haya un chisme útil allí ...

Answer 5

Para abordar específicamente su pregunta "¿Qué es esto?", La ha respondido usted mismo:

Sospecho que esto se debe a que “los valores de columna se vuelcan utilizando el conjunto de caracteres binarios. En efecto, no hay conversión de conjuntos de caracteres ". - dev.mysql.com/doc/refman/5.0/en/select-into.html

Esa es la forma en que MySQL almacena los datos codificados en utf8 internamente. Es una variación terriblemente ineficiente del almacenamiento Unicode, que aparentemente usa tres bytes completos para la mayoría de los caracteres y no admite secuencias UTF-8 de cuatro bytes.

En cuanto a cómo convertirlo a UTF-8 real usando INTO OUTFILE ... No lo sé. El uso de otros métodos mysqldump lo hará sin embargo.

Answer 6

Pruebe SET CHARACTER SET <blah> antes de su selección, <blah>=utf8 o <blah>=utf8 etc ... Consulte: http://dev.mysql.com/doc/refman/5.6/en/charset-connection.html

O SET NAMES utf8; Podría funcionar...

Answer 7

Puede ejecutar consultas de MySQL usando la herramienta CLI (creo que incluso con un formato de salida para que imprima CSV) y redirigir a un archivo. Debería hacer la conversión de caracteres y seguir dándole acceso a hacer uniones, etc.

Answer 8

charset utf8 emitir el charset utf8 en el indicador de MySQL antes de ejecutar SELECT . Esto le dice al servidor como enviar los resultados.