备份数据库某些字符或某些行
备份数据库某些字符或某些行
保存表中所有为M的数据例：
MariaDB [hellodb]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.01 sec)
过滤出Gender M如：
MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M'; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec)
保存到/tmp/下例
：MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M' INTO OUTFILE '/tmp/M.txt'; Query OK, 15 rows affected (0.01 sec)
使用cat可看到如下结果
[root@mysql-master mysql]# cat /tmp/M.txt 1 Shi Zhongyu 22 M 2 3 2 Shi Potian 22 M 1 7 3 Xie Yanke 53 M 2 16 4 Ding Dian 32 M 4 4 5 Yu Yutong 26 M 3 1 6 Shi Qing 46 M 5 N 11 Yuan Chengzhi 23 M 6 N 13 Tian Boguang 33 M 2 N 15 Duan Yu 19 M 4 N 16 Xu Zhu 21 M 1 N 17 Lin Chong 25 M 4 N 18 Hua Rong 23 M 7 N 23 Ma Chao 23 M 4 N 24 Xu Xian 27 M N N 25 Sun Dasheng 100 M N N
仿照 students表结构，创建一个表叫做Ms，然后导入/tmp/M.txt例：
MariaDB [hellodb]> SELECT * FROM students WHERE Gender='M' INTO OUTFILE '/tmp/M.txt'; Query OK, 15 rows affected (0.01 sec)
例：
MariaDB [hellodb]> CREATE TABLE Ms LIKE students; Query OK, 0 rows affected (0.03 sec)
例：
MariaDB [hellodb]> DESC Ms; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | | Gender | enum('F','M') | NO | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | | TeacherID | int(10) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.02 sec)
导入：导入：
MariaDB [hellodb]> LOAD DATA INFILE '/tmp/M.txt' INTO TABLE Ms; Query OK, 15 rows affected (0.00 sec) Records: 15 Deleted: 0 Skipped: 0 Warnings: 0
导入完成后查看：
MariaDB [hellodb]> SELECT * FROM Ms; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 15 rows in set (0.00 sec) MariaDB [hellodb]>
这样的备份比较节约空间，但是更适用于备份字段和行！
