Mysql 备份和恢复.sql文件，导入.csv文件

数据备份和恢复

参考：http://www.mysqltutorial.org/mysql-copy-database/

参考：http://www.mysqltutorial.org/how-to-backup-database-using-mysqldump.aspx mysqldump命令的详细介绍，包括参数的使用。

2020-03-08补充

导出纯文本格式数据：

mysql -u root -p -e 'select * from linshi.employee' > employee.sql

另外还可以使用into outfile 命令。但需要设置一下secure_file_priv参数。

备份数据库

mysqldump -u root -p 数据库名 > 路径/xxx.sql

⚠️>代表export

恢复数据库/导入数据库

• 连接mysql，创建新的数据库
• 退出连接，执行如下命令: mysql -uroot -p 新数据库名 < 路径/xxx.sql; ⚠️< 代表import,
• 或者进入控制台，在一个数据库内使用命令：source 路径/xxx.sql

⚠️导入一个下载的xx.sql，到你的本地，先打开这个文件看一下，文件头几行代码，比如：

CREATE DATABASE /*!32312 IF NOT EXISTS*/`classicmodels` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `classicmodels`;

那么进入控制台后，使用source命令，把文件拖拉到zsh控制台，会自动得到路径，然后敲击回车即可。

另一种备份+恢复的方法，更高效！

使用--databases参数：

1. mysqldump -u root -p --databases 数据库名 > 路径/xxx.sql
2. 原数据库数据丢失/损坏，删除原数据库。
3. mysql -u root -p < 路径/xxx.sql 或者在控制台内使用source命令。

--databases参数的作用其实是增加了2行代码到xxx.sql中：

1. create database xxx;
2. use xxx;

最后查看使用：

show tables from 新的数据库名

导入csv文件

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

第一步：建表

create table orderinfo(

orderid int primary key not null ,

userid int,

isPaid varchar(10),

price float,

paidTime varchar(30));

create table userinfo(

userid int primary key,

sex varchar(10),

birth date);

第二步：导数

load data local infile '路径/xxx.csv' into table orderinfo fields terminated by ',';

load data local infile '路径/user_info_utf.csv' into table userinfo fields terminated by ',';

⚠️

a、语句要正确

b、路径不要有中文，是左斜杆，

c、mysql 8.0 导入会报错

The used command is not allowed with this MySQL version。

这是因为安全需要，对于mysql client, 默认为禁止加载本地数据。

解决办法参考了：stackoverflow

SHOW VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = 1;

d, local关键字，当被指定后，会从在客户端主机的client program读文件并发送到server。

⚠️： 要有fields terminated by ',' 是因为csv 文件是以逗号为分割符的

第三步：对 orderinfo 表的日期数据进行规整

a、先把时间格式标准化变成 1993-02-27 。

update orderinfo set paidtime=replace(paidtime,'/','-') where paidtime is not null;

b、然后更新字符串为日期格式，然后才能使用日期函数进行操作，

update orderinfo set paidtime=str_to_date(paidtime,'%Y-%m-%d %H:%i') where paidtime is not null;

⚠️如果报 function str_to_datetime_value 错误，可以用 select * from orderinfo where paidtime='\r' limit 10; 来看一下是否包含了 \r 符号，

如果是包含了，则用下面语句再过滤掉

update orderinfo set paidtime=str_to_date(paidtime,'%Y-%m-%d %H:%i') where paidtime is not null and paidtime <>'\r';

❌MYSQL导入数据出现

The MySQL server is running with the --secure-file-priv option so it cannot execute this

• a.解决办法：https://blog.csdn.net/gb4215287/article/details/79762020
• b.把数据放到secure-file-priv

附带语句：

# 清除表全部数据命令

truncate userinfo；

相当于drop table userinfo后再create table userinfo(column...), 速度比delete from table更快。

MySQL 日期时间 Extract（选取） 函数

1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456

2. date_add()函数

mysql> select date_add("2017-06-15",INTERVAL 10 DAY);
+----------------------------------------+
| date_add("2017-06-15",INTERVAL 10 DAY) |
+----------------------------------------+
| 2017-06-25                             |
+----------------------------------------+

\r符号，\n, 回车和换行的区别

https://www.cnblogs.com/xiaotiannet/p/3510586.html

回车: Carriage Return,

换行：Line Feed。Feed line into the computer,把一行字符输入到计算机中。

符号 ASCII码 意义

\n 10 换行

\r 13 回车CR

在计算机还没有出现之前，有一种叫做电传打字机（Teletype Model 33，Linux/Unix下的tty概念也来自于此）的玩意，每秒钟可以打10个字符。但是它有一个问题，就是打完一行换行的时候，要用去0.2秒，正好可以打两个字符。要是在这0.2秒里面，又有新的字符传过来，那么这个字符将丢失。

于是，研制人员想了个办法解决这个问题，就是在每行后面加两个表示结束的字符。一个叫做“回车”，告诉打字机把打印头定位在左边界；另一个叫做“换行”，告诉打字机把纸向下移一行。这就是“换行”和“回车”的来历，从它们的英语名字上也可以看出一二。

后来，计算机发明了，这两个概念也就被般到了计算机上。那时，存储器很贵，一些科学家认为在每行结尾加两个字符太浪费了，加一个就可以。于是，就出现了分歧。

在Windows中：

• '\r' 回车，回到当前行的行首，而不会换到下一行，如果接着输出的话，本行以前的内容会被逐一覆盖；
• '\n' 换行，换到当前位置的下一行，而不会回到行首；

Unix系统里，每行结尾只有“<换行>”，即"\n"；

Windows系统里面，每行结尾是“<回车><换行>”，即“\r\n”；

Mac系统里，每行结尾是“<回车>”，即"\r"；。

一个直接后果是，Unix/Mac系统下的文件在Windows里打开的话，所有文字会变成一行；而Windows里的文件在Unix/Mac下打开的话，在每行的结尾可能会多出一个^M符号。