¤Ò¤í¤»¤È¤¤¤¤¤Þ¤¹

on "[mysql 09573] Re: MySQL 4.1.2 ¤ÎÆüËܸì¤Î°·¤¤"
   <20040608.115153.52192899.fuji@xxxxxxxxxx>
at Tue, 08 Jun 2004 11:51:53 +0900 (JST)
   Hirofumi Fujiwara <fuji@xxxxxxxxxx> wrote:

> > >   PostgreSQL¤Ç¤Ïset CLIENT_ENCODING=sjis¤Î¤è¤¦¤Ë¥¯¥é¥¤¥¢¥ó¥È¤Î
> > > ʸ»ú¥³¡¼¥É¥»¥Ã¥È¤òÁªÂò¤Ç¤­¤ë¤Î¤ÇƱ¤¸¤è¤¦¤Ê¼ÂÁõ¤Ë¤·¤Æ¤âÎɤ¤¤Î¤Ç
> > > ¤Ï¤Ê¤¤¤«¤È¡£
> > 
> > ¡¡MySQL¤Ç¤âset names sjis¡Ê¤¢¤ë¤¤¤Ïset character set sjis¡Ë¤È¤«¤Ç»ØÄê¤Ç
> > ¤­¤Þ¤¹¤è¡£¤¤¤Á¤ª¤¦¡£
> 
> http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html
> Dynamic System Variables ¤È¤·¤Æ¡¢character_set_client ¤¬¤¢¤ê¤Þ¤¹¡£
> ¤³¤ÎÊÑ¿ô¤Ï GLOBAL or SESSION ¤ÇÊѹ¹¤Ç¤­¤ë¤Î¤Ç¡¢°ì±þº¤¤é¤Ê¤¤¤Î¤Ç¤Ï¡£
(snip)

¤¤¤í¤¤¤í»î¤·¤Æ¤ß¤Þ¤·¤¿¡£´Ö°ã¤Ã¤Æ¤¤¤¿¤é¤Ä¤Ã¤³¤ß¤ª´ê¤¤¤·¤Þ¤¹¡£

¾¯¤Ê¤¯¤È¤â mysql ¥³¥Þ¥ó¥É¤Ë¸Â¤Ã¤Æ¤¤¤¨¤Ð¡¢¸Ä¿ÍŪ¤Ë¤Ï¡¢

  o my.cnf ¤Î [mysqld] ¤Ë default-character-set = utf8 ¤È½ñ¤¤¤Æ¤ª¤¯¡£
    utf8 ¤¬°ìÈÖÂ礭¤Êʸ»ú½¸¹ç¤Ê¤Î¤Ç¡£
  o mysql ¥³¥Þ¥ó¥É¤Ï¤â¤Ã¤Ñ¤é euc-jp ¤Ê´Ä¶­¤Ç»È¤¦¤Î¤Ç¡¢
    my.cnf ¤Î [mysql] ¤Ë default-character-set = ujis ¤È½ñ¤¤¤Æ¤ª¤¯¡£
  o CREATE DATABASE ¤¹¤ë¤È¤­¤Ï sjis ¤Ê¤ê ujis ¤Ê¤êʸ»ú¥³¡¼¥É¤òÌÀ¼¨Åª¤Ë
    »ØÄꤹ¤ë¡£
  o ¤Ò¤È¤Ä¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤ËÊ£¿ô¤Îʸ»ú¥³¡¼¥É¤Î¥Æ¡¼¥Ö¥ë¤òº®ºß¤µ¤»¤Ê¤¤¡£
  o ¤Ò¤È¤Ä¤Î¥Æ¡¼¥Ö¥ë¤ËÊ£¿ô¤Îʸ»ú¥³¡¼¥É¤Î¥«¥é¥à¤òº®ºß¤µ¤»¤Ê¤¤¡£

¤Î¥ë¡¼¥ë¤ò¼é¤ì¤Ð¥È¥é¥Ö¤é¤Ê¤¤¤ó¤¸¤ã¤Ê¤¤¤«¤È»×¤¤¤Þ¤¹¡£

                                  //

# MySQL 4.1.2-alpha-Max-log ¤Ç¤Î¤ªÏá£

¢£ ʸ»ú¥³¡¼¥É¤Ë´Ø¤¹¤ë¥·¥¹¥Æ¥àÊÑ¿ô

¡ü ¼ïÎà
°Ê²¼¤Î 6 ¤Ä¡£

mysql> show variables like 'character\_set\_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_client     | ujis  |
| character_set_connection | ujis  |
| character_set_database   | utf8  |
| character_set_results    | ujis  |
| character_set_server     | utf8  |
| character_set_system     | utf8  |
+--------------------------+-------+

¡ü ¥·¥¹¥Æ¥àÊÑ¿ô¤ÎÍÑÅÓ

character_set_system
  "indentifiers" (¥Æ¡¼¥Ö¥ë̾¤È¤«¥«¥é¥à̾?) ¤ò³ÊǼ¤¹¤ë¤Î¤Ë»È¤ï¤ì¤ë¡£
  utf8¤Ç ¸ÇÄê¡£

character_set_server
  character_set_database ¤Î¥Ç¥Õ¥©¥ë¥ÈÃͤ˱ƶÁ¤¹¤ë¡£
  
character_set_database
  CREATE DATABASE ¤Çʸ»ú¥³¡¼¥É¤¬»ØÄꤵ¤ì¤Ê¤«¤Ã¤¿¾ì¹ç¡¢database ¤Îʸ»ú¥³¡¼
  ¥É¤Ï¤³¤ì¤Ë¤Ê¤ë¡£
  character_set_connection ¤ÎÃͤ˱ƶÁ¤¹¤ë¡£(¸å½Ò)

character_set_client
  ¥¯¥é¥¤¥¢¥ó¥È¤«¤éÅϤµ¤ì¤¿ SQL ʸ¤Ï¤³¤Îʸ»ú¥³¡¼¥É¤Ç¤¢¤ë¤È²ò¼á¤µ¤ì¤ë¡£

character_set_connection
  ¥­¥ã¥é¥¯¥¿¥»¥Ã¥È¥¤¥ó¥È¥í¥Ç¥å¡¼¥µ (Î㤨¤Ð¡Ø_ujis'¤Û¤²'¡Ù)¤¬¾Êά¤µ¤ì¤¿
  SQL ʸÃæ¤Îʸ»úÎó¥ê¥Æ¥é¥ë¤Ï¤³¤Îʸ»ú¥³¡¼¥É¤Ç¤¢¤ë¤È²ò¼á¤µ¤ì¤ë¡£

character_set_results
  ¥µ¡¼¥Ð¡¼¤¬¥¯¥é¥¤¥¢¥ó¥È¤ËÊÖ¤¹·ë²Ì¤ò¤³¤Îʸ»ú¥³¡¼¥É¤ËÊÑ´¹¤¹¤ë¡£
  ¤³¤ÎÊÑ¿ô¤ò NULL ¤Ë¥»¥Ã¥È¤¹¤ë¤È¡¢·ë²Ì¤ËÂФ¹¤ëʸ»ú¥³¡¼¥ÉÊÑ´¹¤ò¤·¤Ê¤¤¤è
  ¤¦¤Ë¤Ç¤­¤ë¡£

# °Ê²¼¡¢Ä¹¤¤¤Î¤Ç "character_set_" ¤Ï "cs_" ¤Ë¾Êά¤¹¤ë¡£

¡ü ¥·¥¹¥Æ¥àÊÑ¿ô¤ÎÀ©¸æÊýË¡

¸ÄÊÌ¤Ë SET character_set_client charset_name ¤Ê¤É¤È¤¹¤ë°Ê³°¤ÎÀ©¸æÊýË¡¡£

SET CHARACTER SET charset_name
  ¢ª cs_client     charset_name
     cs_connection (cs_database¤ÎÃÍ)
     cs_results    charset_name

  SET CHARACTER SET charset_name ¤ò¼Â¹Ô¤¹¤ë¤È¡¢cs_client ¤È
  cs_connection ¤ÎÃͤ¬ charset_name ¤Ë¤Ê¤ê¡¢cs_results ¤ÎÃͤ¬
  cs_database ¤ÎÃͤÈƱ¤¸¤Ë¤Ê¤ë¡£

SET NAMES 'charset_name'
  ¢ª cs_client     charset_name
     cs_connection charset_name
     cs_results    charset_name

mysql --defalt-character-set charset_name
  ¢ª cs_client     charset_name
     cs_connection charset_name
     cs_results    charset_name

mysqld --defalt-character-set charset_name
  ¢ª cs_database   charset_name
     cs_server     charset_name

¢£ ¥·¥¹¥Æ¥àÊÑ¿ô¤Î±Æ¶Á

¡ü mysql ¥³¥Þ¥ó¥É¤Ç INSERT ¤¹¤ë¾ì¹ç

mysql ¥³¥Þ¥ó¥É¤Ç INSERT ¤¹¤ë¾ì¹ç¤Ë¸Â¤Ã¤Æ¡¢¤³¤ì¤é¤Î¥·¥¹¥Æ¥àÊÑ¿ô¤¬¤É¤Î¤è
¤¦¤Ë±Æ¶Á¤¹¤ë¤«´Ñ»¡¤¹¤ë¡£

# °Ê²¼¡¢¤«¤Ê¤ê¼«¿®¤Ê¤¤¤Ç¤¹¡£

¥µ¡¼¥Ð¡¼¤Ï¡¢¥¯¥é¥¤¥¢¥ó¥È¤«¤éÁ÷¤é¤ì¤¿ SQL ʸÃæ¤Îʸ»úÎó¥ê¥Æ¥é¥ë¤Îʸ»ú¥³¡¼
¥É¤ò cs_client ¤«¤é cs_connection ¤ËÊÑ´¹¤¹¤ë¡£¤¿¤À¤·¡¢¥­¥ã¥é¥¯¥¿¥»¥Ã¥È
¥¤¥ó¥È¥í¥Ç¥å¡¼¥µ¤¬»ØÄꤵ¤ì¤Æ¤¤¤ëʸ»úÎó¥ê¥Æ¥é¥ë¤Ë¤Ï¤³¤ÎÊÑ´¹½èÍý¤ÏŬÍѤµ
¤ì¤Ê¤¤¡£
»²¾È: <URL:http://dev.mysql.com/doc/mysql/en/Charset-connection.html>

¤½¤Î¸å¡¢³ÊǼ¤¹¤ëʸ»ú¥³¡¼¥É*1 ¤ËÊÑ´¹¤·¤Æ¥¹¥È¥ì¡¼¥¸¤Ë³ÊǼ¤¹¤ë¡£

  *1 Í¥ÀèÅÙ¤¬¹â¤¤½ç¤Ë°Ê²¼¤Î¤¤¤º¤ì¤«¡£
     1. ¥«¥é¥à¤Îʸ»ú¥³¡¼¥É»ØÄê¡£
     2. ¥Æ¡¼¥Ö¥ë¤Îʸ»ú¥³¡¼¥É»ØÄê¡£
     3. ¥Ç¡¼¥¿¥Ù¡¼¥¹¤Îʸ»ú¥³¡¼¥É»ØÄê¡£

Îã:
# ujis¤Î¡Ø¤¢¡Ù¤òlatin1¤ËÊÑ´¹¤·¤è¤¦¤È¤¹¤ë¤¬¡¢¤Ç¤­¤Ê¤¤¤Î¤Ç¡Ø?¡Ù¤Ë¤Ê¤ë¡£
cat <<EOF | mysql -u hirose31 --default-character-set=ujis test
set character_set_connection = latin1;
show variables like 'character%';
insert into t_ujis values ('A¤¢');
EOF

# ¥¤¥ó¥È¥í¥Ç¥å¡¼¥µ¤ò¤Ä¤±¤ì¤Ð OK
cat <<EOF | mysql -u hirose31 --default-character-set=ujis test
set character_set_connection = latin1;
show variables like 'character%';
insert into t_ujis values (_ujis'B¤¢');
EOF

# cs_client(=ujis) ¤È¥¤¥ó¥È¥í¥Ç¥å¡¼¥µ(=_sjis)¤¬°Û¤Ê¤Ã¤Æ¤¤¤Æ¤âÂç¾æÉס£
# ¢ª ¥¤¥ó¥È¥í¥Ç¥å¡¼¥µ¤¬»ØÄꤵ¤ì¤Æ¤¤¤ë¾ì¹ç¤Ï cs_client ¤«¤é
#    cs_connection ¤Ø¤ÎÊÑ´¹¤Ï¹Ô¤ï¤ì¤Ê¤¤¡£
cat <<EOF | nkf -Es | mysql -u hirose31 --default-character-set=ujis test
set character_set_connection = latin1;
show variables like 'character%';
insert into t_ujis values (_sjis'C¤¢');
EOF

cat <<EOF | mysql -u hirose31 --default-character-set=ujis test
select * from t_ujis;
EOF
s
A?
B¤¢
C¤¢

¡ü cs_connection ¤ÎÃͤËÃí°Õ¤¹¤ë

SET CHARACTER SET charset_name ¤¹¤ë¤È¡¢cs_connection ¤Ï charset_name
¤Ç¤Ï¤Ê¤¯ cs_database ¤ÎÃͤˤʤ롣¤Þ¤¿¡¢cs_database ¤ÎÃͤϥ«¥ì¥ó¥È¥Ç¡¼
¥¿¥Ù¡¼¥¹¤Îʸ»ú¥³¡¼¥É (DATADIR/DBNAME/db.opt¤¬¤¢¤ì¤Ð¤½¤ì¤Ë¡¢¤Ê¤±¤ì¤Ð
cs_server¤ÎÃÍ) ¤Ë¤Ê¤ë¡£

¤Ê¤Î¤Ç¡¢Î㤨¤Ð mysql ¤ÇÀܳ¤·¤Æ latin1 ¤Ê¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë use dbname ¤Ç°Ü
Æ° (¤³¤Î»þÅÀ¤Ç cs_database ¤¬ latin1 ¤Ë¤Ê¤ë) ¤·¤¿¸å¤Ë SET CHARACTER
SET ujis ¤ò¼Â¹Ô¤¹¤ë¤È¡¢ cs_client ¤Ï ujis ¤Ë¤Ê¤ë¤¬ cs_connection ¤¬
(cs_database¤ÈƱ¤¸) latin1 ¤Ë¤Ê¤Ã¤Æ¤·¤Þ¤¦¤Î¤Ç¡¢ÆüËܸì¤Î INSERT ¤Ê¤É¤¬
´üÂÔÄ̤ê¤Ë¤Ê¤é¤Ê¤¤¡£

²óÈò¤¹¤ë¤Ë¤Ï
  * use dbname ¤ÎÁ°¤Ç¤â¸å¤Ç¤â¤¤¤¤¤Î¤Ç SET NAMES 'ujis' ¤¹¤ë¡£
  * mysql --default-character-set=ujis ¤ÇÀܳ¤·¤Æ¡¢SET CHARACTER SET
    ¤â SET NAMES ¤â¼Â¹Ô¤·¤Ê¤¤¡£
¤Ê¤É¤ÎÊýË¡¤¬¤¢¤ë¡£

                                  //

-- 
¤Ò¤í¤»
http://www.irori.org/