問(wèn)個(gè)比較菜的問(wèn)題，大家多包含(那種編碼較流行)

khandielas

我現(xiàn)在想建數(shù)據(jù)庫(kù)，可以存中文，然后要用perl寫script跟這個(gè)數(shù)據(jù)庫(kù)交換信息。

不太明白我要用哪個(gè)charset才對(duì)，也不清楚哪個(gè)比較流行，哪個(gè)以后更容易處理麻煩最少，讀了下mysql的手冊(cè)，好像下面這3個(gè)命令都可以，我要選哪個(gè)好？希望高手指教，在線等

CREATE DATABASE my_1st_db CHARACTER SET utf8;

CREATE DATABASE my_1st_db CHARACTER SET gb2312;

CREATE DATABASE my_1st_db CHARACTER SET gbk;

[ 本帖最后由 楓影誰(shuí)用了 于 2009-4-17 13:39 編輯 ]

楓影誰(shuí)用了

第一個(gè)比較流行

khandielas

多謝，好像這樣就可以了。我把命令都寫下來(lái)：

mysql> CREATE DATABASE my_1st_db CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| my_1st_db |
+--------------------+

mysql> use my_1st_db;
Database changed
mysql> create table my_1st_table (USR varchar(30), INFO varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> desc my_1st_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| USR | varchar(30) | YES | | NULL | |
| INFO | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> insert into my_1st_table set USR='光明', INFO='學(xué)習(xí)中';
Query OK, 1 row affected (0.00 sec)

mysql> select * from my_1st_table;
+--------+-----------+
| USR | INFO |
+--------+-----------+
| 光明 | 學(xué)習(xí)中 |
+--------+-----------+

然后我寫了個(gè)小的perl script來(lái)試驗(yàn)：
#!/usr/bin/perl
use strict;
use warnings;
use DBI();
use utf8;

my $data_source = 'my_1st_db';
my $username = 'XXXXXXXX';
my $password = 'XXXXXXXX';

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=$data_source",
                         "$username", "$password",
                         {'RaiseError' => 1});
                                         
my $sql = "INSERT INTO my_1st_table VALUES('齊秦', '狼')";
$dbh->do($sql);

my $ref = $dbh->selectall_arrayref(
               "SELECT USR, INFO FROM my_1st_table",
           { Slice => {} }
         );
foreach my $q ( @$ref ) {
        print "USER: $q->{USR} => INFO: $q->{INFO}\n";
}

ubuntu>./test_mysql.pl
USER: 光明 => INFO: 學(xué)習(xí)中
USER: 齊秦 => INFO: 狼

khandielas

繼續(xù)test regex

好像要decode一下才能用regrex。

我加了這個(gè)
use Encode;

        if (decode('utf8', $usr->{USR}) =~ /秦/) {

                print "USER: $usr->{USR} => INFO: $usr->{INFO}\n";
        }

這次輸出的只有齊秦
ubuntu>.\test_mysql.pl
USER: 齊秦 => INFO: 狼
USER: 齊秦 => INFO: 狼

khandielas

query出來(lái)的都要decode一下才能作regrex么，有沒(méi)有更好的辦法？

如果程序長(zhǎng)的話，這個(gè)decode的地方太多了啊。


我又試驗(yàn)了一下讀入一個(gè)文本文件， 沒(méi)有問(wèn)題， code 如下：

open IN, "<", 'test_my_sql_01.input';
while ( <IN> ) {
        chomp;
        my ($usr, $info) = split /\|/;
        print $usr, ' ==>> ', $info, "\n";
        my $sql = "INSERT INTO my_1st_table set USR=\'$usr\', INFO=\'$info\'";
    $dbh->do($sql);
}

khandielas

加中文的條件就必須設(shè)定幾個(gè)數(shù)據(jù)庫(kù)參數(shù)

mysql> SET character_set_client = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_results = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SET character_set_connection = utf8;

然后加中文的條件也可以作query了。

my $ref = $dbh->selectall_arrayref(
"SELECT USR, INFO FROM my_1st_table WHERE USR='光明'",
{ Slice => {} }
);
foreach my $q ( @$ref ){
print "USER: $q->{USR} => INFO: $q->{INFO}\n";
}

$ ./test_mysql_query_with_condition.pl
USER: 光明 => INFO: 學(xué)習(xí)中

小結(jié)：

mySQL用utf8是可行的，如果想方便的話，直接在server level就把default 的charset 變成 utf8 也可以， 應(yīng)用utf8的時(shí)候要注意定義column的時(shí)候用varchar, 可以節(jié)約空間。

perl 內(nèi)部支持unicode,但是輸出和輸入的時(shí)候要轉(zhuǎn)換。

khandielas

最后作個(gè)show variables, 可以看到雖然server的charset還是latin1，database level的charset都變成了utf8.

mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| 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/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
......