Skip to content

Instantly share code, notes, and snippets.

@rc1021

rc1021/infile-users.sql

Created September 18, 2024 01:31
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rc1021/da3bc5d4107f55078be11aa50a4168c4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rc1021/da3bc5d4107f55078be11aa50a4168c4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. rc1021 created this gist Sep 18, 2024.
    11 changes: 11 additions & 0 deletions infile-users.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,11 @@
    use DEMO;

    LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
    INTO TABLE bankList
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 LINES
    (id, email, password, firstName, lastName, cellPhone, created_at, updated_at);

    -- 2024-06-07 02:31:52+00:00 1 sec
    22 changes: 22 additions & 0 deletions outfile-users.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    use DEMO;

    SET time_zone = '+08:00';

    SET NAMES utf8mb4;

    SELECT 'id', 'email', 'password', 'firstName', 'lastName', 'cellPhone', 'created_at', 'updated_at'
    UNION ALL
    SELECT
    id AS `id`,
    email AS 'email',
    password AS 'password',
    firstName AS 'firstName',
    lastName AS 'lastName',
    cellPhone AS 'cellPhone',
    created_at AS 'created_at',
    updated_at AS 'updated_at'
    FROM `users`
    INTO OUTFILE '/var/lib/mysql-files/users.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';