Mysql导入数据：编码问题和权限问题（Linux）

1、创建数据库

CREATE DATABASE `itpv` CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';

2、更改数据库编码

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

 SET character_set_client='utf8';
 SET character_set_connection='utf8';
 SET character_set_results='utf8';

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

3、创建表：

create table iptv1000w( OriginTime datetime, DayId int, TimeId int, DeviceId BINARY(16),  StationId BINARY(16),  

Action nvarchar(128),   SubSystem nvarchar(128),   BranchId int,    RowVersion timestamp, AssetID nvarchar(300),

ContentId BINARY(16), TuneUrl nvarchar(1024),  PlaylistUrl nvarchar(1024),  IsPF bit)default charset=utf8;

4、导入数据：mysql默认目录是/tmp

load data infile '/tmp/IPTV/EventClientTrickState20140616to22/IPTV1000W.csv' into table iptv1000w fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n' ignore 1 lines;（Linux）

可能有的时候不能导入出问题：ERROR 29 (HY000): File '/tmp/IPTV/EventClientTrickState20140616to22/IPTV1000W.csv' not found (Errcode: 13)

把该文件的所属用户和所属组都修改为mysql

该问题导致的原因可能是因为将最后权限 GRANT ALL PRIVILEGES ON *.* TO '%'@'%' IDENTIFIED BY "123" with grant option;(123为密码)覆盖了之前设置的权限

进入mysql

use mysql;

select user,host from user;查看用户及权限

delete user from user='%';

则不许用更改文件的所属用户和所属组都修改为mysql，可正常导入导出数据

导出：select * from table1 into outfile '/tmp/xx.csv'

 

load data local infile 'E:/IPTV1000W.csv' into table iptv1000w fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n' ignore 1 lines;（Win7）