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

数据备份和恢复

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参数：

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

原数据库数据丢失/损坏，删除原数据库。

mysql -u root -p

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

create database xxx;

use xxx;

最后查看使用：

show tables from 新的数据库名

导入csv文件

第一步：建表

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。

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, 回车和换行的区别

回车: 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符号。