目录
---
■创建数据库
create database csdn DEFAULT CHARACTER SET utf8mb4;
■创建用户表
create table user
(
userid varchar(33)NOT NULL,
password varchar(33)NOT NULL,
mail varchar(33)NOT NULL
)Engine=MyISAM
DEFAULT CHARACTER SET utf8mb4;
■导入数据
〇
load data infile "C:\user.sql"
into table user
fields terminated by " # "
lines terminated by "\r\n";
---
select count(*) from user;
---
delete from user;
×
load data infile "C:\data\user.sql"
into table user
fields terminated by " # "
lines terminated by "\r\n";
・数据文件内容
user001 # password001 # aaa@sina.com
user002 # password002 # bbb@sina.com
user003 # password003 # bbb@sina.com
・除去导入数据中的特殊字符(不然报错,1300)
ERROR 1300 (HY000): Invalid utf8 character string: '̨’
--
查找特殊字符的项目,并置换为luanma
[^a-zA-Z0-9,;$%:@()<>{}="'`~/# .\r\n_&\-\+\*\*\!\\\?\^\[\]\|]
---
1.根据以上正则表达式,全部置换为luanma
2.再把luanmaluanma置换为luanma // 重复几次之后,就都OK了。
・导入之后的效果
■出错解决
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
・解决尝试1(NG)
1.执行如下命令
show variables like '%secure%';
2.修改my.ini
secure_file_priv="C:\data\"
secure_file_prive=null ––限制mysqld 不允许导入导出
secure_file_priv=/path/ – --限制mysqld的导入导出只能发生在默认的/path/目录下
secure_file_priv=’’ – --不对mysqld 的导入 导出做限制
3.重启mysql
---
---
・解决方法2(NG) 但距离成功很近了
secure_file_priv=""
---
ERROR 29 (HY000): File 'C:\Program Files\mysql-5.7.22-winx64\data\datauser.sql' not found (Errcode: 2 - No such file or directory)
---
×
load data infile "C:\data\user.sql"
into table user
fields terminated by " # "
lines terminated by "\r\n";
・解决方法3(OK) // 本地(windows)操作
※1:secure_file_priv="" // 配置文件中
※2:数据的SQL文件,要放到mysql的Date目录下面
※3:load data infile "C:\user.sql" // 直接这么写,不要写全路径
※4:注意行,的换行符
把数据放入上面的路径(※2)中后,执行下面的语句
〇
load data infile "C:\user.sql"
into table user
fields terminated by " # "
lines terminated by "\r\n";
■增加ID字段,方便查找
alter table user
add ID int primary key auto_increment first;
---
---
■数据库文件的构造
・data目录下的文件
・数据库目录下的文件
---