是否有任何sqlite命令或第三方工具允许数据库转储在INSERT INTO语句中包含列名称？
代替
INSERT INTO "MyTable" VALUES ('A','B');
我想看
INSERT INTO "MyTable" (Column1,Column2) VALUES ('A','B');
sqlite中的.dump命令只提供第一个版本。
让我再来一个裂缝。
将列名和INSERT语句转储到文件。
sqlite> .output test.data sqlite> pragma table_info(test); sqlite> .dump test sqlite> .quit $ cat test.data 0|test_id|int|0||1 1|test_name|varchar(35)|0||0 PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE test (test_id int primary key,test_name varchar(35)); INSERT INTO "test" VALUES(1,'Wibble'); INSERT INTO "test" VALUES(2,'Wobble'); INSERT INTO "test" VALUES(3,'PernicIoUs'); COMMIT;
现在运行这个awk脚本
/\|/ {
split($0,col_name,"|");
column_names[++n] = col_name[2];
}
/INSERT INTO \"[A-Za-z].*\"/ {
insert_part = match($0,/INSERT INTO \"[A-Za-z].*\"/);
printf("%s ",substr($0,RSTART,RLENGTH));
printf("(");
for (i = 1; i <= n; i++) {
if (i == 1) {
printf("%s",column_names[i]);
}
else {
printf(",%s",column_names[i]);
}
}
printf(") ");
values_part = substr($0,RLENGTH+1,length($0) - RSTART);
printf("%s\n",values_part);
}
我们得到
$ awk -f dump_with_col_names.awk test.data INSERT INTO "test" (test_id,test_name) VALUES(1,'Wibble'); INSERT INTO "test" (test_id,test_name) VALUES(2,'Wobble'); INSERT INTO "test" (test_id,test_name) VALUES(3,'PernicIoUs');
