MySQL資料匯入到infobright中

1. 在mysql中建一張表：

mysql> create table guoqing(
    ->    id int,
    ->    guo char(10),
    ->    qing char(10)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
2. 插入資料：

mysql> insert into guoqing(id,guo,qing) values('10000','1000','1');
Query OK, 1 row affected (0.00 sec)
 
3. 將資料匯出csv檔案：
 最優通用匯出語句
mysql> select * from guoqing into outfile '/tmp/guoqing.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';                
Query OK, 1 row affected (0.00 sec)
其中optionally enclosed by '"'可以給匯出的值加上雙引號，避免值需要轉義
官方關於可選fields引數
  Use the TERMINATED BY sub clause to specify the character recognized as the separator (delimiter) between values. By default, a semicolon ';' is assumed to separate values.
  Use the ENCLOSED BY sub clause to specify the character that begins and ends each string representing a text value. By default, a double quotation mark '"' is assumed to enclose each value. If the text values in the input fle do not use any enclosing characters, use the value 'NULL' in the ENCLOSED BY sub clause. Note that this is the same as using the empty string '' option in standard MySQL.
  Use the ESCAPED BY sub clause to support special characters that may be imbedded within text felds.

4. 在infobright中建一個表：
 參見官方手冊中支援的資料型別


mysql> create table guoqing(
    ->    id int,
    ->    guo char(10),
    ->    qing char(10)
    -> ) ENGINE=brighthouse DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
 
5. 匯入csv到infobright表中：
 
匯入前先確認字符集是否支援中文，如果不支援，設定字符集，防止中文亂碼。
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_server = 'utf8';   
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_database ='utf8';   
Query OK, 0 rows affected (0.00 sec)

mysql> load data infile '/tmp/guoqing.csv' into table guoqing fields terminated by ',' optionally enclosed by  '"' lines terminated by '\n';                                
Query OK, 1 row affected (0.04 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0
 
5，驗證：
mysql> select * from guoqing;
+-------+------+------+
| id    | guo  | qing |
+-------+------+------+
| 10000 | 1000 | 1    |
+-------+------+------+
1 row in set (0.00 sec)
 
這樣mysql中的資料就能匯入到infobright中了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24742969/viewspace-1982569/，如需轉載，請註明出處，否則將追究法律責任。