sqlite3

https://sqlite.org/cli.html

sudo apt install sqlite3

sqlite3 xx.db

重建表结构

CREATE TABLE nc_g2kj___MemberTable_new (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at DATETIME,
  updated_at DATETIME,
  created_by VARCHAR,
  updated_by VARCHAR,
  nc_order REAL,
  ...
);


INSERT INTO nc_g2kj___MemberTable_new (
  created_at, updated_at, created_by, updated_by, nc_order,
  QQ, IDTY, WeiXin, WeiBo, DouYin, KuaiShou,
  field_3, field, field_5, field_4, field_8, wxid,
  field_9, Facebook, field_10, 关键词, field_1, TEST,
  地区, 关联葡萄, field_7, 基督徒倾向, field_2
)
SELECT
  created_at, updated_at, created_by, updated_by, nc_order,
  QQ, IDTY, WeiXin, WeiBo, DouYin, KuaiShou,
  field_3, field, field_5, field_4, field_8, wxid,
  field_9, Facebook, field_10, 关键词, field_1, TEST,
  地区, 关联葡萄, field_7, 基督徒倾向, field_2
FROM nc_g2kj___MemberTable;

DROP TABLE nc_g2kj___MemberTable;
ALTER TABLE nc_g2kj___MemberTable_new RENAME TO nc_g2kj___MemberTable;

增加主键

CREATE INDEX idx_field_2_unique ON nc_g2kj___MemberTable(field_2);
CREATE UNIQUE INDEX idx_field_2_unique ON nc_g2kj___MemberTable(field_2);

找出重复:

SELECT field_2, COUNT(*) AS count
FROM nc_g2kj___MemberTable
GROUP BY field_2
HAVING count > 1;

删除重复：

DELETE FROM nc_g2kj___MemberTable
WHERE id NOT IN (
  SELECT MIN(id)
  FROM nc_g2kj___MemberTable
  GROUP BY field_2
);

复制表

INSERT INTO nc_g2kj__爬号 (
  -- 这里列出所有目标表的字段，注意用实际字段名替换
  id, created_at, updated_at, created_by, updated_by, nc_order,
  QQ, WeiXin, WeiBo, DouYin, KuaiShou,
  field_3, field, field_5, field_4, field_8, wxid,
  field_9, Facebook, field_10, 关键词, field_1, TEST,
  地区, 关联葡萄, field_7, 基督徒倾向, 手机
)
SELECT
  id, created_at, updated_at, created_by, updated_by, nc_order,
  QQ, WeiXin, WeiBo, DouYin, KuaiShou,
  field_3, field, field_5, field_4, field_8, wxid,
  field_9, Facebook, field_10, 关键词, field_1, TEST,
  地区, 关联葡萄, field_7, 基督徒倾向, field_2
FROM nc_g2kj__全部爬号数据;