Как поменять кодировку в sql shell

django-noob

Для обычного виндоадмина PostgreSQL — как взрыв мозга и разрыв GUI шаблонов ))
И так задача — нужно создать пользователя БД. Средство администрирования pgAdmin не позволяет создать пользователя для определенной БД. Можно создать роль, но как привязать её к БД средствами GUI интерфейса совсем непонятно. Ладно, берем утилиту psql.exe, запускаем и получаем грозное сообщение:

C:\Programs\PostgreSQL\9.0\bin>psql -U postgres

psql (9.0.10)
ПРЕДУПРЕЖДЕНИЕ: Кодовая страница консоли (866) отличается от основной
страницы Windows (1251).
8-битовые (русские) символы могут отображаться некорректно.
Подробнее об этом смотрите документацию psql, раздел
«Notes for Windows users».
Введите «help», чтобы получить справку.

Можно впасть в депрессию, представив что нужно будет читать иероглифы, полученные отображением cp1251 под dos866. Но не всё потеряно. Рецепт из доки:

Как поменять кодировку в sql shell

An important restriction, however, is that each database’s character set must be compatible with the database’s LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other libc-provided locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) If you have ICU support configured, ICU-provided locales can be used with most but not all server-side encodings.

24.3.1. Supported Character Sets

Table 24.1 shows the character sets available for use in PostgreSQL .

Table 24.1. PostgreSQL Character Sets

Name	Description	Language	Server?	ICU?	Bytes/​Char	Aliases
BIG5	Big Five	Traditional Chinese	No	No	1–2	WIN950 , Windows950
EUC_CN	Extended UNIX Code-CN	Simplified Chinese	Yes	Yes	1–3
EUC_JP	Extended UNIX Code-JP	Japanese	Yes	Yes	1–3
EUC_JIS_2004	Extended UNIX Code-JP, JIS X 0213	Japanese	Yes	No	1–3
EUC_KR	Extended UNIX Code-KR	Korean	Yes	Yes	1–3
EUC_TW	Extended UNIX Code-TW	Traditional Chinese, Taiwanese	Yes	Yes	1–3
GB18030	National Standard	Chinese	No	No	1–4
GBK	Extended National Standard	Simplified Chinese	No	No	1–2	WIN936 , Windows936
ISO_8859_5	ISO 8859-5, ECMA 113	Latin/Cyrillic	Yes	Yes	1
ISO_8859_6	ISO 8859-6, ECMA 114	Latin/Arabic	Yes	Yes	1
ISO_8859_7	ISO 8859-7, ECMA 118	Latin/Greek	Yes	Yes	1
ISO_8859_8	ISO 8859-8, ECMA 121	Latin/Hebrew	Yes	Yes	1
JOHAB	JOHAB	Korean (Hangul)	No	No	1–3
KOI8R	KOI 8-R	Cyrillic (Russian)	Yes	Yes	1	KOI8
KOI8U	KOI 8-U	Cyrillic (Ukrainian)	Yes	Yes	1
LATIN1	ISO 8859-1, ECMA 94	Western European	Yes	Yes	1	ISO88591
LATIN2	ISO 8859-2, ECMA 94	Central European	Yes	Yes	1	ISO88592
LATIN3	ISO 8859-3, ECMA 94	South European	Yes	Yes	1	ISO88593
LATIN4	ISO 8859-4, ECMA 94	North European	Yes	Yes	1	ISO88594
LATIN5	ISO 8859-9, ECMA 128	Turkish	Yes	Yes	1	ISO88599
LATIN6	ISO 8859-10, ECMA 144	Nordic	Yes	Yes	1	ISO885910
LATIN7	ISO 8859-13	Baltic	Yes	Yes	1	ISO885913
LATIN8	ISO 8859-14	Celtic	Yes	Yes	1	ISO885914
LATIN9	ISO 8859-15	LATIN1 with Euro and accents	Yes	Yes	1	ISO885915
LATIN10	ISO 8859-16, ASRO SR 14111	Romanian	Yes	No	1	ISO885916
MULE_INTERNAL	Mule internal code	Multilingual Emacs	Yes	No	1–4
SJIS	Shift JIS	Japanese	No	No	1–2	Mskanji , ShiftJIS , WIN932 , Windows932
SHIFT_JIS_2004	Shift JIS, JIS X 0213	Japanese	No	No	1–2
SQL_ASCII	unspecified (see text)	any	Yes	No	1
UHC	Unified Hangul Code	Korean	No	No	1–2	WIN949 , Windows949
UTF8	Unicode, 8-bit	all	Yes	Yes	1–4	Unicode
WIN866	Windows CP866	Cyrillic	Yes	Yes	1	ALT
WIN874	Windows CP874	Thai	Yes	No	1
WIN1250	Windows CP1250	Central European	Yes	Yes	1
WIN1251	Windows CP1251	Cyrillic	Yes	Yes	1	WIN
WIN1252	Windows CP1252	Western European	Yes	Yes	1
WIN1253	Windows CP1253	Greek	Yes	Yes	1
WIN1254	Windows CP1254	Turkish	Yes	Yes	1
WIN1255	Windows CP1255	Hebrew	Yes	Yes	1
WIN1256	Windows CP1256	Arabic	Yes	Yes	1
WIN1257	Windows CP1257	Baltic	Yes	Yes	1
WIN1258	Windows CP1258	Vietnamese	Yes	Yes	1	ABC , TCVN , TCVN5712 , VSCII

The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII , the server interprets byte values 0–127 according to the ASCII standard, while byte values 128–255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII . Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.

24.3.2. Setting the Character Set

initdb defines the default character set (encoding) for a PostgreSQL cluster. For example,

sets the default character set to EUC_JP (Extended Unix Code for Japanese). You can use —encoding instead of -E if you prefer longer option strings. If no -E or —encoding option is given, initdb attempts to determine the appropriate encoding to use based on the specified or default locale.

You can specify a non-default encoding at database creation time, provided that the encoding is compatible with the selected locale:

This will create a database named korean that uses the character set EUC_KR , and locale ko_KR . Another way to accomplish this is to use this SQL command:

Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data. For more information see Section 23.3.

The encoding for a database is stored in the system catalog pg_database . You can see it by using the psql -l option or the \l command.

Important

On most modern operating systems, PostgreSQL can determine which character set is implied by the LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.

PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX . As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.

24.3.3. Automatic Character Set Conversion Between Server and Client

PostgreSQL supports automatic character set conversion between server and client for many combinations of character sets (Section 24.3.4 shows which ones).

To enable automatic character set conversion, you have to tell PostgreSQL the character set (encoding) you would like to use in the client. There are several ways to accomplish this:

Using the \encoding command in psql . \encoding allows you to change client encoding on the fly. For example, to change the encoding to SJIS , type:

libpq (Section 34.11) has functions to control the client encoding.

Using SET client_encoding TO . Setting the client encoding can be done with this SQL command:

Also you can use the standard SQL syntax SET NAMES for this purpose:

To query the current client encoding:

To return to the default encoding:

Using PGCLIENTENCODING . If the environment variable PGCLIENTENCODING is defined in the client’s environment, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

Using the configuration variable client_encoding. If the client_encoding variable is set, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

If the conversion of a particular character is not possible — suppose you chose EUC_JP for the server and LATIN1 for the client, and some Japanese characters are returned that do not have a representation in LATIN1 — an error is reported.

If the client character set is defined as SQL_ASCII , encoding conversion is disabled, regardless of the server’s character set. (However, if the server’s character set is not SQL_ASCII , the server will still check that incoming data is valid for that encoding; so the net effect is as though the client character set were the same as the server’s.) Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.

24.3.4. Available Character Set Conversions

PostgreSQL allows conversion between any two character sets for which a conversion function is listed in the pg_conversion system catalog. PostgreSQL comes with some predefined conversions, as summarized in Table 24.2 and shown in more detail in Table 24.3. You can create a new conversion using the SQL command CREATE CONVERSION . (To be used for automatic client/server conversions, a conversion must be marked as “ default ” for its character set pair.)

Table 24.2. Built-in Client/Server Character Set Conversions

Server Character Set	Available Client Character Sets
BIG5	not supported as a server encoding
EUC_CN	EUC_CN , MULE_INTERNAL , UTF8
EUC_JP	EUC_JP , MULE_INTERNAL , SJIS , UTF8
EUC_JIS_2004	EUC_JIS_2004 , SHIFT_JIS_2004 , UTF8
EUC_KR	EUC_KR , MULE_INTERNAL , UTF8
EUC_TW	EUC_TW , BIG5 , MULE_INTERNAL , UTF8
GB18030	not supported as a server encoding
GBK	not supported as a server encoding
ISO_8859_5	ISO_8859_5 , KOI8R , MULE_INTERNAL , UTF8 , WIN866 , WIN1251
ISO_8859_6	ISO_8859_6 , UTF8
ISO_8859_7	ISO_8859_7 , UTF8
ISO_8859_8	ISO_8859_8 , UTF8
JOHAB	not supported as a server encoding
KOI8R	KOI8R , ISO_8859_5 , MULE_INTERNAL , UTF8 , WIN866 , WIN1251
KOI8U	KOI8U , UTF8
LATIN1	LATIN1 , MULE_INTERNAL , UTF8
LATIN2	LATIN2 , MULE_INTERNAL , UTF8 , WIN1250
LATIN3	LATIN3 , MULE_INTERNAL , UTF8
LATIN4	LATIN4 , MULE_INTERNAL , UTF8
LATIN5	LATIN5 , UTF8
LATIN6	LATIN6 , UTF8
LATIN7	LATIN7 , UTF8
LATIN8	LATIN8 , UTF8
LATIN9	LATIN9 , UTF8
LATIN10	LATIN10 , UTF8
MULE_INTERNAL	MULE_INTERNAL , BIG5 , EUC_CN , EUC_JP , EUC_KR , EUC_TW , ISO_8859_5 , KOI8R , LATIN1 to LATIN4 , SJIS , WIN866 , WIN1250 , WIN1251
SJIS	not supported as a server encoding
SHIFT_JIS_2004	not supported as a server encoding
SQL_ASCII	any (no conversion will be performed)
UHC	not supported as a server encoding
UTF8	all supported encodings
WIN866	WIN866 , ISO_8859_5 , KOI8R , MULE_INTERNAL , UTF8 , WIN1251
WIN874	WIN874 , UTF8
WIN1250	WIN1250 , LATIN2 , MULE_INTERNAL , UTF8
WIN1251	WIN1251 , ISO_8859_5 , KOI8R , MULE_INTERNAL , UTF8 , WIN866
WIN1252	WIN1252 , UTF8
WIN1253	WIN1253 , UTF8
WIN1254	WIN1254 , UTF8
WIN1255	WIN1255 , UTF8
WIN1256	WIN1256 , UTF8
WIN1257	WIN1257 , UTF8
WIN1258	WIN1258 , UTF8

Table 24.3. All Built-in Character Set Conversions

[a] The conversion names follow a standard naming scheme: The official name of the source encoding with all non-alphanumeric characters replaced by underscores, followed by _to_ , followed by the similarly processed destination encoding name. Therefore, these names sometimes deviate from the customary encoding names shown in Table 24.1.

24.3.5. Further Reading

These are good sources to start learning about various kinds of encoding systems.

Contains detailed explanations of EUC_JP , EUC_CN , EUC_KR , EUC_TW .

The web site of the Unicode Consortium.

Prev	Up	Next
24.2. Collation Support	Home	Chapter 25. Routine Database Maintenance Tasks

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Корректная настройка MySQL для работы с UTF8

Сегодня речь пойдет о MySQL и о настройке UTF8 кодировки по-умолчанию. Тема заезжена, но как я убедился за прошедшую неделю, мало кто в состоянии нормально пояснить какие параметры и куда надо прописать для полноценной работы с UTF8 в MySQL. К сожалению, ситуация на тематических блогах оставляет желать лучшего. Основной тип ответа — приведение соедржимого конфигурационного файла с комментарием типа “попробуй, у меня это работает”.

Основная цель данного поста — выяснить, какие параметры и с какими значениями следует прописать в конфигурационный файл my.cnf (my.ini) для дальнейшей беспроблемной работы с Юникодом.

Рабочее окружение

UTF8 на данный момент у меня успешно работает в Мастер-Слейв конфигурации:

• MySQL версии 5.1.66
• Два сервера CentOS версии 6.3
• Репликация между серверами Master-Slave на базе SSL

Любой внешний клиент в состоянии корректно работать с UTF8 базой (проверено на EMS Manager for MySQL c Windows 8 x64).

Все опции и настройки я привожу для версии сервера 5.1.x, однако с минимальными (а то и вовсе без оных) изменениями все это будет работать и на версиях 5.5.x и 5.6.x.

Параметры кодировок MySQL

Довольно часто приходится видеть в ответах на вопросы о настройке UTF8 следующее:

Предполагается, что после вставки всего этого добра (тут кстати есть противоречащие друг другу опции) в конфигурационный файл my.cnf (my.ini) магический Юникод начнет работать.

Но давайте забудем о списке и попытаемся разбираться со всеми опциями сами и начнем с самого начала. То есть с документации. Потому как все это прекрасно описано в документации MySQL на официальном сайте. Я лишь постараюсь последовательно рассказать о параметрах сервера и прояснить неясные моменты.

Главный раздел по описанию кодировок (character sets) и их представлений (collations — используется например при сортировке) в контексте сервера, базы, таблиц — это секция 10.1.3. Specifying Character Sets and Collations.

Символьная кодировка может быть задана для:

1. сервера,
2. базы данных,
3. таблицы и
4. колонок в таблице.

Сделано это для гибкой настройки баз данных и доступа клиентов с разными кодировками. Однако, последнее не входит в область рассмотрения данного поста, поэтому будем рассматривать вариант с кодировкой UTF8 настроенной для всего по-умолчанию.

Все параметры могут быть переданы серверу тремя разными способами:

1. через командную строку mysqld
2. через конфигурационный файл my.cnf (my.ini)
3. через опции компиляции.

Второй и третий варианты рассматриваться не будут. Тут уместно будет просто прочитать официальные доки — в каждом разделе приведены примеры конфигурации с использованием всех трех способов. Я же буду использовать первый вариант.

Кодировка (character set) и представление (collation) сервера

Кодировка (characher set) — набор используемых символов.
Представление (collation) — набор правил для сравнения символов в наборе.

Тут есть несколько фундаментальных вещей которые надо понимать.

Основные параметры используемые в контексте сервера — это character_set_server и collation_server . Оба параметра влияют на определение кодировки и отображения сервера MySQL.

Можно задать оба параметра либо только один из них. При этом важно знать как задача того или иного влияет на определение отсутствующего:

Не заданы — используются значения по умолчанию (дефолтные),

Заданы оба — используются указанные кодировка и ее представление,

Задана только кодировка — ее представление выставляется по умолчанию для данного типа кодировки. Что это значит? Для каждого типа кодировки есть ее дефолтное представление, например, дефолтная кодировка сервера — latin1 , а дефолтное отображение для нее — latin1_swedish_ci . Посмотреть соответствие кодировки и ее дефолтного представления можно используя команду:

Поле Default дает ответ о представлении выбранной кодировки.

В нашем случае, при настройке дефолтной кодировки в UTF8, параметры должны быть определены, так как могут быть использованы при определении кодировки или представления базы данных:

Наши команды:
my.cnf (my.ini)

[mysqld]
character-set-server = utf8
collation-server = utf8_unicode_ci

Дефолтное представление для utf8 — utf8_general_ci , так что если бы мы его использовали вместо utf8_unicode_ci , то параметр collation_server можно было бы вообще опустить.

Кодировка (character set) и представление (collation) базы данных

Тут есть два варианта определения кодировки и представления:

явно — при выполнении запроса на создание базы данных:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

неявно через переменные character_set_database и collation_database . Однако, эти переменные нельзя задать явно ни в командной строке ни в конфигурационном файле. Как они инициализируются — чуть ниже.

Вообще при работе с базой данных огромную роль помимо серверных настроек играют настройки клиент-серверного соединения (connection). На этом этапе вступают в игру следующие специфичные для соединения параметры:

• character_set_client — кодировка в которой посылается запрос от клиента
• character_set_connection — кодировка используемая для конвертации пришедшего запроса (statement’а)
• character_set_results — кодировку, в которую сервер должен перевести результат перед его отправкой клиенту

Есть еще представление кодировки соединения ( colation_connection ). Для чего нужен этот параметр думаю пояснять не надо.

Озадачиваться проблемой инициализации всех этих переменных не стоит (хотя в нашем случае присвоить им значения необходимо). Есть способ проще: существует два типа запросов (statements) которые задают настройки соединения клиента с сервером группой:

Запрос SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]

Параметр определяет в какой кодировке теперь будут приходить сообщения для сервера от клиента. Прелесть в том, что запрос SET NAMES x эквивалентен следующей группе:

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

Для определении представления кодировки соединения ( colation_connection ) отличного от дефолтного, следует дополнить запрос:

SET NAMES x COLLATE y

А так как у нас utf8 и ее дефолтное представление utf8_general_ci , то нам нужно выпонить полный запрос:

SET NAMES utf8 COLLATE utf8_unicode_ci

Таким образом, используя только этот запрос, можно добиться корректной UTF8 инициализации соединения.

Однако, тут есть один нюанс:

SET NAMES x , как понятно из определения, определяет настройку клиента при коннекте к серверу. Но что делать, если клиент — сам mysql.exe и нам хочется установить collation_connection по-умолчанию, не выполняя каждый раз SET NAMES x при коннекте?
Для этих целей, существует еще один параметр — default_character_set . Он эквивалентен запросу SET NAMES utf8 . В случае его использования задать collation_connection отличный от дефолтного уже не получится, поэтому придется заюзать еще одну команду init_connect (так как напрямую collation_connection нельзя прописать в конфигурационном файле):

init_connect=‘SET collation_connection = utf8_unicode_ci’

Но и тут есть еще одно но: init_connect команда не выполняется для SUPER пользователей — пользователей, обладающих привилегией SUPER. root входит в этот перечень, поэтому при коннекте root’ом команду SET collation_connection = utf8_unicode_ci все же придется выполнить вручную.

Запрос SET CHARACTER SET charset_name

Запрос групповой и он также эквивалентен следующей группе:

SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;

Согласно документации, разница между двумя запросами в том, что параметры character_set_connection и collation_connection будут установлены на @@character_set_database и @@collation_database соответственно (выше я про них упоминал).

За более детальной информацией отсылаю по двум источникам — собственно к официальной документации и прекрасно оформленному ответу на stackoverflow.com. Для нашей задачи вполне хватает первого параметра вместе с дополнительной командой.

Подытожим: различные сценарии и что юзается на каждом из них — относительно к настройкам соединения:

• Если к базе коннектится mysql.exe клиент с пользователем с привилегией SUPER:
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • надо выполнить вручную команду init_connect=’SET collation_connection = utf8_unicode_ci’
  • срабатывает опция в конфигурационном файле default_character_set = utf8
  • срабатывает команда в конфигурационном файле init_connect=’SET collation_connection = utf8_unicode_ci’
  • надо выполнить вручную команду SET NAMES utf8 COLLATE utf8_unicode_ci

  Наши команды:
  my.cnf (my.ini)

  [client]
  default_character_set = utf8

  [mysqld]
  init_connect=‘SET collation_connection = utf8_unicode_ci’

  Кодировка (character set) и представление (collation) таблиц

  Тут все довольно просто. Задать кодировку и ее представление можно через команды:

  CREATE TABLE t1 ( … )
  CHARACTER SET utf8 COLLATE utf8_unicode_ci;

  Тут главное иметь в виду, что если эти настройки не заданы, то берутся настройки базы данных (см. пред. раздел). Нам эти настройки не интересны.

  Кодировка (character set) и представление (collation) колонок в таблице

  Тут по аналогии с пред. секцией. Если параметры кодировок не указаны, берутся те, что указывались для таблицы.

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

  skip-character-set-client-handshake

  Помимо освещенных параметров, есть еще один довольно часто фигурирующий в разного рода источниках — skip-character-set-client-handshake. Установка этого параметра позволит проигнорировать информацию клиента о кодировке. Я данный параметр не использовал.

  Верификация настроек

  Итак, вот финальный snapshot наших изменений в файле my.cnf (my.ini):

  [mysqld]
  init_connect=‘SET collation_connection = utf8_unicode_ci’
  character-set-server = utf8
  collation-server = utf8_unicode_ci

  [client]
  default-character-set = utf8

  После применения всех опций и рестарта сервера mysql для проверки настроек можно воспользоваться командами SHOW VARIABLES LIKE ‘char%’ и SHOW VARIABLES LIKE ‘collation%’ ;

  Состояние среды до изменений:

  Состояние среды после изменений (в случае, если вы приконнектились не SUPER пользователем):

  Для примера, вот отличие при соединении через mysql.exe пользователем с и без привилегии SUPER:

  с привилегией и выполненной вручную командой ‘SET collation_connection = utf8_unicode_ci’:

  Поздравляю, теперь ваши база, таблицы и все в таблицах по-умолчанию в кодировке UTF8.

  Как поменять кодировку в sql shell

  5.0 > SET NAMES koi8r;
  Query OK, 0 rows affected (0.00 sec)

  shell> mysqldump mydb > mydb.sql
  shell> cat mydb.sql
  CREATE TABLE mytable (
  id int(11) NOT NULL auto_increment,
  data char(10) default NULL,
  PRIMARY KEY (id)
  ) TYPE=MyISAM;

  5.0 > SET NAMES koi8r;
  Query OK, 0 rows affected (0.00 sec)

  5.0 > SET character_set_server=koi8r;
  Query OK, 0 rows affected (0.00 sec)

  5.0 > ALTER TABLE mytable MODIFY data BINARY(10);
  Query OK, 3 rows affected (0.03 sec)
  Records: 3 Duplicates: 0 Warnings: 0

  5.0 > ALTER TABLE mytable MODIFY data CHAR(10) CHARSET koi8r;
  Query OK, 3 rows affected (0.01 sec)
  Records: 3 Duplicates: 0 Warnings: 0

  Похожие публикации:

  1. Как настроить перевод страницы в яндекс браузере
  2. Как настроить солнце в 3d max corona
  3. Как поменять разрешение экрана через командную строку
  4. Почему все сайты невозможно установить безопасное соединение