MySQL バルクインサート

• MySQL 複数データのバルク INSERT や CSV ファイルから高速インポートする方法
• 【MySQL】LOAD DATA INFILE するときのファイルの文字コード

@main.command()
@click.argument("source_file")
@click.option("--encoding", "-e", default="cp932")
@click.pass_context
def data_import(ctx, source_file, encoding):
    """ import TSV file """

    opt = get_model()._meta
    fields = ",".join([f.name for f in opt.fields if f.name not in ["id"]])
    SQL = f"""
    LOAD DATA LOCAL INFILE '{source_file}' INTO TABLE {opt.db_table}
    CHARACTER SET {encoding}
    FIELDS TERMINATED BY '\t' ({fields}) ;
    """

    with connection.cursor() as cursor:
        cursor.execute(SQL)
        print(cursor.fetchall())

重複時に UPDATE する(REPLACE):

    LOAD DATA LOCAL REPLACE
    INFILE '{source_file}'
    INTO TABLE {opt.db_table}
    CHARACTER SET {encoding}
    FIELDS TERMINATED BY '\t' ({fields}) ;

-local-infile=1 オプションが必要

settings.DATABASES:

{
  "ENGINE": "django.db.backends.mysql",
  ...
  "OPTIONS": { "local_infile": 1 },
  "ATOMIC_REQUESTS": false,
  "AUTOCOMMIT": true,
  "CONN_MAX_AGE": 0,
  "TIME_ZONE": null
}

データベース間でテーブルデータのコピー

OPTS="--no-create-info --skip-extended-insert --set-gtid-purged=OFF --complete-insert"
INI="--defaults-file=.mysql.ini"
DB=yourdb

mysqldump $INI $OPTS $DB purchases_purchase | gzip > tables/purchases_purchase.sql.gz

echo "truncate table purchases_purchase" | mysql $INI  --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" $DB
zcat tables/purchases_purchase.sql.gz  | mysql $INI --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" $DB

同一データベースｄないでテーブルコピー

• [MySQL] テーブルをコピーする

-- スキーマを完全コピー後、別途を入れる
CREATE TABLE newtable LIKE oldtable;
INSERT INTO newtable SELECT * FROM oldtable;

-- インデックスなどのｗ制約を別途作る必要があるが、データーも同時に入る
CREATE TABLE newtable SELECT * FROM oldtable;