带有列名的 SQLite 导出 [英] SQLite export with column names

问题描述

是否有任何 SQLite 命令或第三方工具允许数据库转储在 INSERT INTO 语句中包含列名?

Is there any SQLite command or third-party tool that allows database dumps to include column names in the INSERT INTO statements?

代替

INSERT INTO "MyTable" VALUES ('A', 'B');

我想看看

INSERT INTO "MyTable" (Column1, Column2) VALUES ('A', 'B');

SQLite 中的 .dump 命令仅提供第一个版本.

The .dump command in SQLite only offers the first version.

推荐答案

让我再试试这个.

将列名和 INSERT 语句转储到文件中.

Dump column names and INSERT statements to a file.

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 脚本

Now run this awk script

/\|/ {
  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');

这篇关于带有列名的 SQLite 导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！