数据库21mysql导出导入带header的csv

mysql spj -uroot -p123456 -e “select * from s;” > C:\backup\s.txt

echo "select * from table into outfile '/tmp/table.csv' fields terminated by ',' escaped by '\\' lines terminated by '\n';"|/usr/bin/mysql -uroot database

SELECT * FROM test INTO OUTFILE "E:\\test\\a.csv" FIELDS TERMINATED BY "," ENCLOSED BY ''  
 LINES TERMINATED BY '\n'

mysqldump -u root -phello886 -t  -T ~/tmp/ db_name table_name --fields-terminated-by=','
-T制定目录
-w 增加条件判断语句
--result-file=file_name, -r file_name
直接输出到指定文件中。该选项应该用在使用回车换行的系统上（例如：DOS，Windows）。
该选项确保只有一行被使用。
示例：mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt

SELECT '用户ID', '姓氏', '名字', '电子邮箱'
UNION ALL
SELECT id, first_name, last_name,email
FROM kalacloud_users
INTO OUTFILE '/tmp/kalacloud_users_out_c.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

SET @table_name = "a";
SET @schema_name = "mydb";
SET @output_name = CONCAT("C://",@table_name,".csv");
 
SET @cols = NULL;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'") ORDER BY ordinal_position SEPARATOR ",") INTO @cols
			from INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @schema_name;
 
SET @sql = CONCAT(" SELECT * FROM (SELECT ", @cols, " UNION ALL SELECT * FROM ", @table_name, ") as r ",
				  " INTO OUTFILE '", @output_name, 
				  "' FIELDS TERMINATED BY '\\t' ");
 
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

# write the table into 1.log file
$MYSQL -u root -p****** >1.log << EOF #1.log中是tab分割的sql结果
$statement
EOF

#cat the 1.log file and convert it to csv file
cat 1.log | while read line
do
echo $line | tr " " ","    #sql结果被转为逗号分割的csv
done > $database"_"$table.csv

mysqldump -uroot -p dbname > dbname .sql
mysql -u用户名 -p密码 数据库名 < 数据库名.sql
mysql -h10.253.29.10 -uroot -p密码 -e"source /opt/rh/test.sql"

mysql>source /home/xxxx/dbname.sql;

load data infile 'C:\\Users\\UserName\\Desktop\\test.csv'
into table `table`
fields terminated by ',' optionally enclosed by '"' escaped by '"'
lines terminated by '\n';

echo "load data infile "/tmp/table.csv" replace into table table fields escaped by '\\' terminated by ',' lines terminated by '\n';"|/usr/bin/mysql -uroot database

mysqlimport -L -uroot -proot db01 table_name.txt；
--fields-terminated-by=字符串：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值为制表符“\t”。
-L, --local：表示从客户端任意路径读取文件导入表中，未设置该选项时，默认只从datadir下同名数据库目录下读取文件导入
--ignore-lines=n：表示可以忽略前n行。
-l, --lock-tables：写入时锁定所有表
-p, --password[=name]：指定用户密码
-u, --user=name：指定登入MySQL用户名
-h, --host=name：指定远程连接的服务器
-c, --columns=name：往表里导入指定字段，如：--columns='Name,Age,Gender'
-C, --compress：在客户端和服务器之间启用压缩传递所有信息
--replace	--replace和--ignore选项控制对唯一键值重复现有行的输入行的处理
用法：--replace, -r

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/address_book.csv' 
INTO TABLE address_book 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS;

load data infile 'student.csv' into table student
FIELDS TERMINATED BY ','   -- csv字段分隔符
ENCLOSED BY '"'            
LINES TERMINATED BY '\n'   -- csv行分隔符
IGNORE 1 ROWS              -- 去除表头
(@col1,@col2,@col3,@col4)  -- @colN 代表csv文件的第N列的值
set name=@col1,class=@col2,description=@col3,score=@col4;  -- 每个列分别赋值

LOAD DATA INFILE 'C:\\Users\\Administrator\\Desktop\\ky\\grxx.csv'
INTO TABLE `grxx`          #要插入的表
CHARACTER SET utf8         #设置表编码
FIELDS TERMINATED BY ','   #字段以,分隔
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"'             #字段以"包围
LINES TERMINATED BY '\r\n' #换行符表示一行结束
 IGNORE 1 LINES            #忽略第一行
 (`index`, `ybh`, `idnum`, `name`, `sex`, `birthday`, `companycode`, `companyname`, `phone`, `dept`);  #csv文件中数据按此顺序插入表中

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/address_book.csv' 
INTO TABLE address_book 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS
(Mobile,@Date,Address)
SET Date = STR_TO_DATE(@Date, '%m/%d/%Y');