数据库备份SQL查询 [英] Database backup SQL Query
问题描述
我需要以插入语句的形式从数据库中获取数据以进行备份
I need to fetch data from database for backup in the form of insert statements
我需要在c#中单击一个按钮来执行此操作.因此,我认为使用sql查询或存储过程而不是mysqldump更为合适.
I need to do it on a button click in c#. So i think an sql query or stored procedure will be appropriate to do this, rather than mysqldump.
第二,所有表格都需要它们.而不是写表和列的名称.应该从information_schema中获取它们,因为无需针对不同的scema更改查询.
Secondly I need them for all tables. Instead of writing table and column names. They should be fetched from information_schema, because the query will not need to be changed for different scema
如果已经存在解决方案,请指导我.
If there already exists a solution, please guide me.
更新:我已经准备了一种解决方案,它已经发布,仍在寻找更好的解决方案.
Update : I have prepared a solution, it is posted, still looking for the better one.
推荐答案
获取数据整个数据库-SqlFiddle演示
仅获取一个表的数据--SqlFiddle演示
To get Data of whole database - SqlFiddle Demo
To get data of only one table - - SqlFiddle Demo
我提出了一个复杂但可以接受的解决方案. 但需要改进.
I have made a complex but acceptable solution. But needs improvement.
这是一个复杂的过程,具有复杂的编码,尤其是查询,该查询通过group_concat
将所有列的所有行提取到单个结果中,并使用复杂的级联进行格式化.
This is a complex procedure with complex coding especially the query which fetches all rows of all columns into a single result by group_concat
and formats with a complex concatenation.
需要简化,高效并且在所有情况下都可以工作.
Need it simplified, efficient and working in all scenarios.
我的解决方案的一些详细信息:以下是重要的部分,其他仅是条件/循环(我不方便使用文档,它也需要时间和建议,有人可能会帮助我设置格式并的改进,给您带来的不便,我们深表歉意.但是,如果您和我有任何帮助,我将很高兴.)
Some details of my solution : Following is the important part, other is just conditions/Looping (I am not handy with documentation also it needs time and suggestions, someone might help me in its formatting and improvement, Sorry for any inconvenience, however I will be glad for any help from you and me)
注意:group_concat(yourColumn分隔符'--anySeparator--')将列的所有行合并为一个,这样行被--anySeparator--分隔>
Note: group_concat(yourColumn separator ' --anySeparator-- ') is merging all rows of your column as one such that Rows are separated by --anySeparator--
select group_concat(column_name separator '`,`') into @cns1 from
information_schema.columns where table_schema=dn and table_name=@tn;
1:column_names作为单个值得到,
1 : column_names are got as a single value separated by
`,` => @cs1 = id`,`ename`,`did
select group_concat(column_name separator '`,"\',\'",`') into @cns2
from information_schema.columns where table_schema=dn and table_name=@tn;
2:column_names作为单个值得到,
2 : column_names are got as a single value separated by
`','` => @cn2 = id`','`ename`','`did
set @cns1=concat("`",@cns1,"`"); set @cns2=concat("`",@cns2,"`");
3:缺少字母(`)放在列名的开头和结尾
3: Missing letter (`) is put at beginning and end of Column names
set @res=concat(@res," insert into ",@tn,"(",@cns1,") values ('");
4:只需使res= " insert into emp(
`id` ,
`ename` ,
`did` ) values("
在这里您可以看到为什么要放置分隔符(实现了MySql格式)
4: Simply makes res= " insert into emp(
`id` ,
`ename` ,
`did` ) values("
Here you can see why have I put separators (MySql Formatting is achieved)
set @temp := '';
set @q := concat("select group_concat(concat(",@cns2,") separator \"'),('\")
from ",dn,".",@tn, " into @temp");
以上是最关键的语句:它从表中获取所有数据行作为单列的行,并且进一步合并这些行,并用'),('
Above is the most crucial statement It gets all data rows from table as rows of a single column and further these rows are merged being separated by '),('
5.1 concat(",@cns2,")
在一列中获取所有列的值.
5.1 concat(",@cns2,")
gets values of all columns in a single one.
5.2现在最外面的连拍之后@q是
5.2 After outer most concat now @q is
@q = "select group_concat(`id`','`ename`','`,did` separator '),(' from
mydb.emp into @temp";
5.3:group_concat将合并的列的所有行合并为一个值.
列值将通过@ cns2中存在的分隔符进行连接,而行级别的连接将使用'),('
5.3 : group_concat will merge all rows of that combined column into one value.
Columns values will be joined through separators existing in @cns2 and rows level joining will be with '),('
prepare s1 from @q;
execute s1;deallocate prepare s1;
set @res = concat(@res,@temp,");");
@q被执行
set @res = concat(@res,@temp,");");
6:我们将得到结果
res was = insert into emp(`id`,`ename`,`did`) values ('
@temp = 1','e1','4'),('2','e2','4'),
('3','e3','2'),('4','e4','4'),('5','e5','3
在@res = concat(@res,@temp,");");
之后,我们得到了
insert into emp(`id`,`ename`,`did`) values ('1','e1','4'),('2','e2','4'),
('3','e3','2'),('4','e4','4'),('5','e5','3);
这篇关于数据库备份SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!