Mysql 5.7 memuat file data

Banyak klien saya mengatakan kepada saya bahwa mereka tidak suka menggunakan pernyataan LOAD DATA INFILE dan lebih suka mengurai dan memuat data secara manual. Alasan utama mereka melakukannya adalah masalah dengan set karakter, khususnya UTF8MB4 dan memuat data infile. Ini mengejutkan saya karena saat ini semua orang menggunakan UTF8. MySQL5. 7 (serta 5. 6) memiliki dukungan penuh untuk UTF8MB4, yang akan memperbaiki masalah yang tersisa (mis. e. , Anda sekarang dapat memuat emoji baru, seperti 🐬)

Minggu lalu saya sedang menyelidiki kasus menarik di mana kami memuat data dan mendapatkan kesalahan berikut

1

2

3

4

5

6

7

8

mysql - e 'pilih versi()'

+----------+

. versi() .

+----------+

. 5. 7. 12    .

+----------+

$ mysql - vvv testdb < load_data.sql

ERROR 1300 (HY000) at line 1: Tidak valid utf8mb4 karakter string. 'Casa N'

Pernyataan memuat data

1

2

3

4

5

6

7

8

9

10

11

12

LOAD DATA LOKAL INFILE

                           'masukan. psv'

                        GANTI MENJADI TABEL

                            masukan

                        KARAKTER SET

                            utf8mb4

                        FIELDS

                            DIHENTIKAN OLEH '. '

                        GARIS

                           DIHENTIKAN OLEH 'rn'

                        ABAIKAN

                            1 GARIS

Tabel menggunakan kumpulan karakter yang benar (kumpulan karakter global diterapkan ke semua bidang varchar)

1

2

3

4

5

6

BUAT TABEL `masukan` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  . . .

  `alamat` varchar(255) DEFAULT NULL,

  . . .

) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4

Talinya tampak seperti “Casa Nº 24”. Jadi ini harus N + U+00BA (MASCULINE ORDINAL INDICATOR, hex code. c2ba). Ketika saya melakukan “lebih sedikit masukan. tsv", ini menunjukkan N Jadi mengapa MySQL tidak dapat memuatnya?

Setelah penyelidikan lebih lanjut, kami menemukan pengkodean asli bukan UTF8. KAMI menemukan dengan berlari

1

2

$ file - i masukan.tsv

masukan. tsv. teks / polos; set karakter=iso-8859-1

So the code was misleading. Also, when I got the actual character from the file, it was just one byte (UTF8 for this character should be two bytes). When MySQL parsed the UTF8 input file, it found only the first part of the multibyte UTF8 code and stopped with an error.

Karakter asli dalam hex adalah "ba"

1

2

xxd - p char_ascii

ba0a

(0a adalah carriage return, dan "ba" adalah "indikator ordinal maskulin")

Setara dengan UTF8

1

2

$ xxd - p char_utf8

c2ba0a

Ini sekarang menjadi dua byte (+ carriage return). c2ba

Untuk mengatasi masalah tersebut kita cukup mengubah CHARACTER SET utf8mb4 menjadi CHARACTER SET latin1 saat melakukan load data infile. Ini memperbaiki masalah

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Kueri OK, 2 baris affected (0.00 dtk)

Rekaman. 2  Dihapus. 0  Dilewati. 0  Peringatan. 0

mysql> set nama utf8mb4;

Kueri OK, 0 baris affected (0.00 dtk)

mysql> pilih alamat dari input;

+--------------------------------+

. alamat_penerima              .

+--------------------------------+

. Casa N º 24 . . . . . . . . . . . . . . . . . . . .

. . .

+--------------------------------+

2 baris di set (0.00 dtk)

Opsi lain adalah mendeteksi pengkodean set karakter (iconv dapat melakukannya) dan mengonversi ke UTF8

Tapi itu berhasil sebelumnya…?

Ini bekerja sedikit berbeda di MySQL 5. 6

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

$ mysql - e 'pilih versi()

+-------------+

. versi()   .

+-------------+

. 5. 6. 25 - 73. 0 .

+-------------+

$ mysql - vvv testdb < load_data.sql

. . .

Kueri OK, 2 baris affected, 2 warnings (0.00 dtk)

Rekaman. 2  Dihapus. 0  Dilewati. 0  Peringatan. 2

--------------

tampilkan peringatan

--------------

+--------+------+--------------------------------

. Tingkat   . Kode . Pesan                                                             .

+--------+------+--------------------------------

. Peringatan . 1366 . Nilai string nilai salah. 'xBA 24. ' untuk kolom 'alamat' di row 1  |

. Peringatan . 1366 . Nilai string nilai salah. 'xBA 24. ' untuk kolom 'alamat' di row 2  |

+--------+------+--------------------------------

2 baris di set (0.00 dtk)

MySQL5. 7 lebih ketat dan tidak mengizinkan Anda memasukkan data dalam format yang salah. Namun, itu tidak 100% konsisten. Untuk beberapa karakter, MySQL 5. 7 juga akan memberikan peringatan jika menonaktifkan mode SQL yang ketat

Karakter lain yang menyebabkan masalah yang sama adalah xC9. Saat memuat ke MySQL 5. 7 dengan sql_mode default (ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION) itu menimbulkan kesalahan

1

ERROR 1366 (HY000) at line 1: Nilai string nilai salah. 'xC9' untuk kolom 'alamat' at row 1

Saat menonaktifkan mode ketat, sekarang defaultnya adalah peringatan

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

mysql> set global sql_mode = '';

Kueri OK, 0 baris affected (0.00 dtk)

Kueri OK, 2 baris affected, 1 warning (0.00 dtk)

Rekaman. 1  Dihapus. 1  Dilewati. 0  Peringatan. 1

--------------

tampilkan peringatan

--------------

+--------+------+--------------------------------

. Tingkat   . Kode . Pesan                                                      .

+--------+------+--------------------------------

. Peringatan . 1366 . Nilai string nilai salah. 'xC9' untuk kolom 'address' at row 1  |

+--------+------+--------------------------------

1 baris di set (0.00 dtk)

Emoji di MySQL

Dengan dukungan UTF8MB4 (di MySQL 5. 6 dan 5. 7), Anda juga dapat memasukkan lumba-lumba kecil ke dalam tabel MySQL

Bagaimana cara memuat data infile di MySQL?

Bagaimana cara mengaktifkan memuat data infile lokal di MySQL?

Apakah MySQL5. 7 masih didukung?

Versi MySQL mana yang memuat opsi XML?