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

最新推荐文章于 2021-06-09 08:09:30 发布

转载最新推荐文章于 2021-06-09 08:09:30 发布 · 169 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/xiaomila-study/p/4975014.html

文章标签：

#数据库

本文详细介绍了如何在数据库中创建、更改编码、创建表并导入数据，包括使用Linux和Windows系统的具体步骤，以及解决导入数据时遇到的问题，如文件路径错误和权限问题。

1、创建数据库

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

2、更改数据库编码

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）

转载于:https://www.cnblogs.com/xiaomila-study/p/4975014.html