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/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/24742969/viewspace-1982569/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值