一个清空数据库数据的脚本总结

迈不过友情╰ 2022-01-07 22:37 410阅读 0赞

测试时,有时要清空数据库很多数据,由于有些表有关联,所以干脆所有表数据都清除.  
我的思路是:先备份一个只有表结构的sql,再备份一些保存业务配置的表,再把原来的数据库drop,再创建一个新的数据库,再source一下还原表结构,再还原保存业务配置的表.由于经常要清空数据库,于是写了一个小脚本,还是学到很多东西.  
这个小脚本花了两天时间,我是做测试的,也是一个很怕代码的人,一直在逃避.有时可以写些小脚本,但是要花费更多的时间好像不值得.所以就每次都是手动做.要改变思维,克服困难.加油.

## 1.保留数据表结构,只删除所有表数据 ##

#### 方法1:导出一个没有数据的数据库,再drop原来的数据库,create 一个新的(空的,没有表结构),再source一下之前导出的没有数据的sql ####

mysqldump -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} --no-data >villadb-nodata.sql
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} -e "drop database ${DATABASE}"
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} -e "create database ${DATABASE}"

#### 方法2:之前看到一个用truncate的方法,因为truncate效率高,只删除数据,跟delete类似,又有所不同.上面有说到两者的区别. ####

mysql -uroot -pengine -h127.0.0.1 -e "select CONCAT('truncate TABLE ',table_schema,'.',table_name) from information_schema.TABLES where table_schema ='villadb'" > all.sql
    
    vim all.sql,把第一行删掉,在命令行模式下输入":1,$ s/$/;/g" ,所有行尾加";"
    
    [clouder@ana53 useful-sql]$ mysql -uroot -pengine -h127.0.0.1 -e "source all.sql"
    ERROR 1701 (42000) at line 5 in file: 'all.sql': Cannot truncate a table referenced in a foreign key constraint (`villadb`.`ContinuousPublishInform`, CONSTRAINT `ContinuousPublishInform_ibfk_1` FOREIGN KEY (`projectOutputId`) REFERENCES `villadb`.`ContinuousPublish` (`id`))
    ERROR 1146 (42S02) at line 23 in file: 'all.sql': Table 'villadb.ProjectCloudView' doesn't exist
    ERROR 1146 (42S02) at line 31 in file: 'all.sql': Table 'villadb.ServiceOffering' doesn't exist
    ERROR 1701 (42000) at line 40 in file: 'all.sql': Cannot truncate a table referenced in a foreign key constraint (`villadb`.`ContinuousPublishInform`, CONSTRAINT `ContinuousPublishInform_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `villadb`.`User` (`id`))
    报错是因为有些表有关联,所以无法被删除,所以推荐用第一个方法删除数据库所有表的数据.这只是一个思路哈.

![1461765-20190202153117032-1397852058.png][]

## 总结学习点: ##

#### 1.mysql -e cmd 可以用shell操作mysql,不在mysql提示符下操作,非交互式,适合嵌入shell脚本 ####

#### 2.shell 单引号和双引号的区别,单引号属于强引用,不对引号内任何特殊字符转义,只当普通字符.双引号属于弱引用,会对括起来的特殊字符做处理.如$,>等.上面的例子用到$\{DATABASE\},要用双引号,才能保留$\{\}的特殊意义,即传递一个变量,而不是简单字符串. ####

#### 3.mysqldump --no-data 参数(跟-d 一样),表示只导出数据库表结构,不导出数据.用-t则表示只导出数据,不导出表结构. ####

#### 4.删除数据库有3种方法:(delete有所保留,可以回滚,drop更彻底) ####

*  delete from table where  
    直接删除表中的某一行数据，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。所以delete相比较truncate更加占用资源，数据空间不释放，因为需回滚。对table和view都能操作
 *  truncate table  
    一次性地从表中删除所有的数据(释放存储表数据所用的数据页来删除数据)并不把单独的删除操作记录记入日志保存(只在事务日志中记录 页的释放)，因此也不能回滚，不能恢复数据，在删除的过程中不会激活与表有关的删除触发器，占用资源更加少，速度更快。数据空间会释放，这个表和索引所占用的空间会恢复到初始大小。只能操作没有关联视图的table  
    由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器，对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。  
    truncate table不能用于参与了索引视图的表
 *  drop table  
    删除的是整个表，包括表的结构，数据，定义。永久抹去，空间释放。对table和view都能操作

#### 5.vim 的六种模式 ####

*  普通模式Normal mode:默认进入就是普通模式,可以移动,和用delete删除
 *  插入模式Insert mode:按a,A,i,I,o,O都可以进入.a表示在当前字符后面追加,A表示在行末,,i表示在光标位置插入,I表示在行首,o表示在下一行,O表示在上一行;
 *  可视模式Visual mode:按ctrl+v进入,可以用来排版,如多行缩进:ctrl+v进入可视模式,光标选中连续多行,按"shift+>"或"shift+<"批量缩进
 *  选择模式Select mode:不清楚
 *  命令行模式CommandLine mode:按Esc退出到命令行模式,输入":"执行命令,"/"加关键字表示从当前行向下搜索,"?"加关键字表示从当前行向上搜索
 *  Ex模式Ex mode:不清楚

#### 6.echo 带颜色显示,试一下吧. ####

\-e是echo的一个可选项，用于激活特殊字符的解析器, 也就是对反斜线转义符（即）的解释。\\033引导非常规字符序列。意味着设置属性然后结束非常规字符序列，这里起效果的字符是  
47;30;5和0。修改47;30;5可以生成不同颜色的组合，数值和编码的前后顺序没有关系

echo -e "\033[20;1H\033[1;4;32m david use echo say \033[0m Hello World \n"
    
    范围
    
    0 重新设置属性到缺省设置
    1 设置粗体
    2 设置一半亮度(模拟彩色显示器的颜色)
    4 设置下划线(模拟彩色显示器的颜色)
    5 设置闪烁
    7 设置反向图象
    8 消隐 
    22 设置一般密度
    24 关闭下划线
    25 关闭闪烁
    27 关闭反向图象
    
    // 字体颜范围(前景颜色):30~39
    30:黑 
    31:红 
    32:绿 
    33:黄 
    34:蓝色 
    35:紫色 
    36:深绿 
    37:白色 
    
    38:在缺省的前景颜色上设置下划线
    39:在缺省的前景颜色上关闭下划线
    
    // 字背景颜色范围(背景颜色):40~49 
    40:黑 
    41:深红 
    42:绿 
    43:黄色 
    44:蓝色 
    45:紫色 
    46:深绿 
    47:白色 
    
    nA 光标上移n行 
    nB 光标下移n行 
    nC 光标右移n行 
    nD 光标左移n行 
    y;xH设置光标位置 
    2J 清屏 
    K 清除从光标到行尾的内容 
    s 保存光标位置 
    u 恢复光标位置 
    ?25l 隐藏光标 
    ?25h 显示光标
    --------------------- 
    作者：魔小明 
    来源：CSDN 
    原文：https://blog.csdn.net/david_dai_1108/article/details/70478826 
    版权声明：本文为博主原创文章，转载请附上博文链接！

#### 7.shell自定义函数 ####

function func\_name()  
\{  
if \[ $1 -eq $2 \];then  
echo "$1 = $2"  
else  
echo "$1 = $2"  
fi  
\}  
func\_name 2 4  
1.shell的函数,不需要在函数定义时写上参数,只需要在调用函数时传递  
2.if判断,用"\[\]"中括号括起,并且前后要留一个空格  
3.比较:数字比较用-eq,-lt,-gt,-le,-ge; 字符串比较用=,!=,in;文件判断用-f,-d,-r,-w

## 调试时用到的技巧 ##

#### 1.shell脚本在开头加上"set -x"表示调试模式,会把每句命令都打印出来,再打印每句命令的执行结果 ####

#### 2.vim中批量注释.用vim命令打开文件 ####

##### 方法1:可视模式visual mode #####

*  批量注释:光标移动到要注释的第一行,按ctrl+v进入块模式,按方向键向下,直到你要注释的最后一行,按大写的i,注意大写的哦,输入\#,再按2次Esc键,就看到选中的行首都增加了一个\#.
 *  取消注释:在块模式下选中要取消注释的行,按一下"d",即可

##### 方法2:替换命令 #####

*  批量注释:按Esc进入命令行模式command mode,输入":set nu",显示行号;再按一下Esc,输入":1,$ s/^/\#/g" 表示从1到最后一行,每个行首都增加\# . 其中"1,$" 表示从1到最后一行,可更改为实际的起始行和结束行
 *  取消注释:在命令行模式,输入":1,$ s/^\#//g"

#### 3.vim命令行下常用设置: ####

nohl:取消高亮  
set nu:显示行数  
set nonu :不显示行数

## 最后附我写的完整脚本 ##

#!/usr/bin/bash
    set -x
    HOSTNAME='127.0.0.1'
    USERNAME='root'
    PASSWORD='engine'
    DATABASE='villadb'
    
    result()
    {
    if [ $? -eq 0 ];then
    echo -e "\033[47;32m ----------- $1 succeed! ---------- \033[0m"
    else
    echo -e "\033[47;31m ----------- $1 failed! ----------- \033[0m"
    fi
    }
    
    echo -e "\033[47;34m ------------ step 1: dump a no-data sql of villadb ------------- \033[0m"
    mysqldump -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} --no-data >villadb-nodata.sql
    result step1
    
    echo -e "\033[47;34m ------------ step 2: dump important config like VDC,ServiceOffering,etc ------------- \033[0m"
    mysqldump -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} VdcType ServiceOffering TotalTemplate VDC Configuration > config.sql
    result step2
    
    echo -e "\033[47;34m ------------ step 3: drop villadb ------------- \033[0m"
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} -e 'drop database villadb'
    result step3
    
    echo -e "\033[47;34m ------------ step 4: create a new villadb ------------- \033[0m"
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME}  -e 'create database villadb IF NOT EXISTS villadb'
    result step4
    
    echo -e "\033[47;34m ------------ step 5: import the backup table-structure ------------- \033[0m"
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} -e 'source /home/clouder/userful-sql/villadb-nodata.sql'
    result step5
    
    echo -e "\033[47;34m ------------ step 6: import the backup config ------------- \033[0m"
    mysql -u${USERNAME} -p${PASSWORD} -h${HOSTNAME} ${DATABASE} -e 'source /home/clouder/userful-sql/config.sql'
    result step6

#### 参考: ####

删除数据库的3种方式:  
https://blog.csdn.net/u010735147/article/details/81872034  
echo 带颜色:  
https://blog.csdn.net/david\_dai\_1108/article/details/70478826  
mysql导出数据库表结构:  
https://www.cnblogs.com/xiaoleiel/p/8316685.html  
mysql只删除数据库所有表数据,保留数据结构:  
https://blog.csdn.net/iw1210/article/details/79586033  
vim的几种模式:  
https://www.cnblogs.com/shiyanlou/archive/2017/09/05/7478346.html  
if判断:  
https://www.cnblogs.com/kairo/p/6646774.html

转载于:https://www.cnblogs.com/xiaozhuangAna/p/10348146.html

[1461765-20190202153117032-1397852058.png]: /images/20211220/9c5d37f93a5e49538568319d2f8088ec.png