MySQL UNSIGNED Primary Keys: Signed or Unsigned, and How to Fix

ByTukang Post

Penafian: Saya bina Schemity, alat ERD desktop — entri ini dari blog kami dan guna alat itu untuk contoh.

TL;DR: Guna tanda yang skema anda sudah pakai, kerana MySQL menolak foreign key yang tandanya berbeza daripada key yang dirujuk. Laravel dan WordPress hasilkan key BIGINT UNSIGNED, sedangkan Rails dan Django hasilkan key BIGINT signed. Untuk skema baru yang belum ada apa-apa menentukan pilihan anda, BIGINT signed lebih mudah. Kalau nanti anda tukar tanda, anda perlu drop setiap foreign key yang merujuk key itu, serta salin table key dan setiap table yang merujuknya.

Primary key MySQL boleh jadi signed atau UNSIGNED, dan kedua-duanya guna storan yang sama: 4 byte untuk INT, 8 byte untuk BIGINT. Yang menentukan pilihan bukan julat, tapi foreign key. MySQL menolak sebarang foreign key yang tandanya berbeza daripada column yang dirujuk, jadi tanda key pertama menentukan tanda setiap column key yang akan merujuknya.

Itulah sebabnya soalan ini biasanya muncul sebagai error, bukan diskusi reka bentuk. Satu migrasi tambah table orders ke database Laravel, menetapkan user_id sebagai BIGINT biasa, dan MySQL balas dengan error 3780. Di bawah ini ialah apa yang anda dapat daripada setiap pilihan, diukur pada MySQL 8.4.11 dalam container pakai buang, cara memilih, dan kos kalau anda berubah fikiran nanti.

Apa yang diubah oleh UNSIGNED pada column integer MySQL

Hanya julat. Jadual integer dalam manual MySQL menetapkan satu saiz storan untuk setiap jenis, sama ada signed atau tidak:

Pada 8.4, insert 2,147,483,648 ke dalam INT signed dan -1 ke dalam INT UNSIGNED kedua-duanya gagal dengan ERROR 1264: Out of range value. Julat berganda itu nyata untuk INT, kerana 2.1 bilion ialah angka yang boleh dicapai table events yang sibuk. Untuk BIGINT, ia hanya teori: kalau insert sejuta row setiap saat, key signed mampu bertahan kira-kira 292,000 tahun.

UNSIGNED bukan dua benda ini. Ia tidak deprecated untuk integer: MySQL 8.0.17 deprecated UNSIGNED untuk DECIMAL, FLOAT dan DOUBLE, dan 8.4 masih beri warning bahawa UNSIGNED untuk data jenis decimal dan floating point adalah deprecated pada jenis itu, tapi bukan pada INT atau BIGINT. Dan (20) dalam BIGINT(20) UNSIGNED ialah display width, deprecated dalam release yang sama. 8.4 beri warning bahawa Integer display width adalah deprecated, dan SHOW CREATE TABLE akan buang ia.

Framework mana yang hasilkan key signed dan unsigned

Kebanyakan skema tidak membuat pilihan. Yang membuat pilihan ialah framework yang mencipta jadual pertama:

Helper Laravel sekarang kekal konsisten antara satu sama lain, tetapi default Laravel pernah berubah sekali. Aplikasi yang dibuat sebelum Laravel 5.8 mencipta users.id dengan $table->increments(‘id’) , yang merupakan INT UNSIGNED , jadi foreignId(‘user_id’) baru yang menunjuk ke sana akan gagal, dan lajur itu perlu $table->unsignedInteger(‘user_id’) sebaliknya. WordPress core tidak mengisytiharkan foreign key langsung, jadi di situ ralat datang daripada plugin atau jadual yang ditulis tangan. Selain itu, masalah muncul di tempat dua konvensyen bertemu: $table->bigInteger(‘user_id’) yang ditulis tangan (bigInteger Laravel adalah signed) bersebelahan dengan $table->id() , aplikasi Laravel yang ditambah di atas pangkalan data yang dicipta terlebih dahulu oleh aplikasi Rails atau raw-SQL, atau jadual pelaporan yang ditulis tangan dalam pangkalan data WordPress.

Untuk melihat konvensyen yang sudah digunakan oleh pangkalan data, kira primary key integer mengikut jenisnya:

SELECT c.COLUMN_TYPE, COUNT(*) AS primary_keys
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME
 AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY'
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.DATA_TYPE IN ('tinyint', 'smallint', 'mediumint', 'int', 'bigint')
GROUP BY c.COLUMN_TYPE;

Satu baris yang dikembalikan bermakna satu konvensyen. Dua baris yang dikembalikan bermakna foreign key seterusnya perlu memeriksa jadual mana yang ia tunjuk.

Adakah primary key MySQL perlu UNSIGNED?

Hanya apabila skema di sekitarnya sudah begitu. Tanda (sign) bukan pilihan setiap jadual, ia konvensyen yang perlu dikongsi oleh seluruh skema, dan pilihan paling murah hampir sentiasa yang sudah ada dalam pangkalan data:

Hujah untuk signed dalam skema baharu ialah segala-galanya di luar jadual. Penolakan ialah salah satunya: pada 8.4, SELECT a.id – b.id antara dua id BIGINT UNSIGNED, 1 tolak 2, gagal dengan ERROR 1690: BIGINT UNSIGNED value is out of range , dan CAST(a.id – b.id AS SIGNED) gagal dengan cara sama, kerana penolakan dijalankan dahulu. Hanya CAST(a.id AS SIGNED) – CAST(b.id AS SIGNED) , atau mod SQL NO_UNSIGNED_SUBTRACTION , yang mengembalikan -1. Porting ialah satu lagi: PostgreSQL, SQL Server dan SQLite tiada integer 64-bit unsigned, jadi kunci BIGINT UNSIGNED sama ada berpindah ke BIGINT dengan separuh atas secara teori yang tidak lagi mampu ditampung, atau ke NUMERIC . long Java , int64 Go dan int PHP juga signed 64-bit, jadi nilai BIGINT UNSIGNED yang melebihi 2^63-1 tiada jenis native dalam bahasa-bahasa itu.

Apa pun pilihan anda, peraturan yang tidak boleh ditawar tetap sama: lajur foreign key mendapat jenis yang sama persis dengan kunci yang dirujuknya, termasuk UNSIGNED.

Mengapa MySQL mengembalikan ralat 3780 untuk foreign key?

Kerana manual MySQL memang menetapkan begitu: “Saiz dan tanda bagi jenis data berketepatan tetap seperti INTEGER dan DECIMAL mesti sama.” Ini kes Laravel, yang berjaya dicuba semula pada 8.4.11:

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    CONSTRAINT posts_user_id_foreign FOREIGN KEY (user_id) REFERENCES users (id)
);
ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'posts_user_id_foreign' are incompatible.

Tiga butiran daripada sesi ujian yang sama:

  • SET foreign_key_checks = 0 pun tidak dapat melepaskannya. CREATE TABLE yang sama masih gagal dengan ralat yang sama. Manual menyenaraikan “mencipta kekangan foreign key di mana satu lajur merujuk kepada jenis lajur yang tidak sepadan” antara perkara yang tetap ditolak walaupun pemeriksaan dimatikan.
  • Tanda sama, saiz berbeza pun tetap gagal. Satu lajur INT UNSIGNED yang merujuk kepada BIGINT UNSIGNED juga mengembalikan ralat 3780.
  • Arah rujukan pun tidak menjadi masalah. Lajur BIGINT UNSIGNED yang merujuk kepada kunci BIGINT signed gagal dengan cara yang sama seperti arah sebaliknya. Laravel foreignId(‘account_id’) juga begitu; ia berbeza dari segi saiz dan tanda, tetapi menunjuk kepada kunci INT signed warisan. Perbaikannya ialah $table->integer(‘account_id’) diikuti oleh $table->foreign(‘account_id’)->references(‘id’)->on(‘accounts’) , supaya ia sepadan dengan kunci tersebut, bukan mengikut lalai framework.

Punca lain bagi ralat 3780: kunci string dengan collation berbeza

Kunci integer bukan satu-satunya kes. Petikan manual yang sama turut menyatakan: “Bagi lajur string bukan binari (karakter), set karakter dan collation mesti sama.” Kunci UUID CHAR(36) atau country_code akan terkena masalah ini setiap kali jadual kunci dan jadual baharu dicipta dengan lalai yang berbeza, dan teks ralatnya sama, jadi ia kelihatan seperti masalah jenis data.

Selalunya, ia berpunca daripada jadual yang dibuat bertahun-tahun lalu. Database yang dicipta pada MySQL 8.0 atau lebih baru secara lalai menggunakan utf8mb4_0900_ai_ci, iaitu lalai server 8.0. Jadual lama selalunya menggunakan utf8mb3_general_ci, manakala config lalai Laravel menulis utf8mb4_unicode_ci ke atas setiap jadual yang dicipta melalui migration. Mana-mana jadual yang dibuat tanpa klausa COLLATE, sama ada melalui raw SQL, GUI atau framework lain, akan mendapat lalai database sebagai gantinya. Pada 8.4.11:

CREATE TABLE tenants (
    id CHAR(36) NOT NULL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    CONSTRAINT projects_tenant_id_foreign FOREIGN KEY (tenant_id) REFERENCES tenants (id)
);
ERROR 3780 (HY000): Referencing column 'tenant_id' and referenced column 'id' in foreign key constraint 'projects_tenant_id_foreign' are incompatible.

Kedua-dua lajur menggunakan utf8mb4 dan kedua-duanya CHAR(36). Cuma collation yang berbeza, dan foreign_key_checks = 0 pun tidak membantu. Mengisytiharkan lajur sebagai tenant_id CHAR(36) COLLATE utf8mb4_unicode_ci NOT NULL berjaya. Untuk melihat kunci mana yang akan menyebabkan table baru gagal sebelum anda membuat table baru, senaraikan lajur primary key yang collation-nya berbeza daripada default database:

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLLATION_NAME,
       s.DEFAULT_COLLATION_NAME AS new_tables_get
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND k.TABLE_NAME = c.TABLE_NAME
 AND k.COLUMN_NAME = c.COLUMN_NAME AND k.CONSTRAINT_NAME = 'PRIMARY'
JOIN information_schema.SCHEMATA s ON s.SCHEMA_NAME = c.TABLE_SCHEMA
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.COLLATION_NAME <> s.DEFAULT_COLLATION_NAME;

Setiap row ialah satu kunci yang lajur foreign key-nya memerlukan COLLATE secara eksplisit. Kunci integer tidak pernah muncul, kerana COLLATION_NAME-nya NULL.

Berapa kos untuk menukar kunci daripada UNSIGNED kepada signed?

Lebih daripada satu ALTER. Dengan orders.user_id merujuk kepada users.id, kedua-duanya BIGINT UNSIGNED, migration yang paling jelas itu gagal pada statement pertama, dengan atau tanpa foreign key checks:

ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT;
ERROR 3780 (HY000): Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'orders_user_id_foreign' are incompatible.

Menukar lajur child dahulu juga gagal dengan cara yang sama. Foreign key itu perlu dibuang, kedua-dua lajur ditukar, dan kunci itu dikembalikan:

ALTER TABLE orders DROP FOREIGN KEY orders_user_id_foreign;
ALTER TABLE users MODIFY COLUMN id BIGINT AUTO_INCREMENT;
ALTER TABLE orders MODIFY COLUMN user_id BIGINT NOT NULL;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_foreign
    FOREIGN KEY (user_id) REFERENCES users (id);

Pada dua table yang masing-masing mempunyai 1,000,000 row, ia mengambil masa 7.3 saat, dan setiap statement selepas drop melaporkan 1,000,000 row affected:

Langkah terakhir ialah langkah yang boleh anda buat lebih murah. Dengan foreign_key_checks = 0, ADD CONSTRAINT yang sama pada table 1,000,000 row hanya affected 0 row dan mengambil masa 0.87 s, kerana MySQL tidak memeriksa row sedia ada. Ini selamat di sini, kerana hanya jenis berubah dan tiada nilai yang berubah, tetapi tidak selamat jika ada row yang mungkin tidak sepadan.

Jadual online DDL menyenaraikan perubahan jenis lajur sebagai “Permits Concurrent DML: No”, jadi penulisan ke setiap table perlu menunggu semasa table itu disalin. Darabkan dengan setiap table yang merujuk users, dan kos pertukaran menjadi jelas: tentukan sign sekali sahaja, pada table pertama, kerana menukarnya kemudian merupakan migration schema merentasi seluruh graf foreign key.

Bagaimana Schemity menangani kunci UNSIGNED

Schemity ialah perisian reka bentuk database yang membaca database live anda, menunjukkan impak setiap perubahan schema sebelum ia dijalankan, dan menyimpan diagram sebagai fail dalam Git. Apabila anda menyambung ke MySQL, ia membaca sign setiap lajur bersama jenisnya, dan canvas menunjukkan lajur unsigned sebagai uBIGINT, jadi konvensi schema, serta mana-mana table yang memecahkannya, boleh dilihat sekali imbas merentasi diagram.

Untuk mereka bentuk yang seterusnya, editor field menyediakan kotak tanda Unsigned pada jenis integer MySQL. Apabila anda menarik garis hubungan, jenis penuh key disalin ke lajur foreign key baharu, termasuk tanda, jadi jadual baharu yang merujuk users.id Laravel akan mendapat user_id BIGINT UNSIGNED dan migration-nya berjaya diterapkan, bukan gagal dengan kod 3780. Character set dan collation bagi key string disalin dengan cara yang sama, jadi lajur yang merujuk tenants.id dicipta dengan COLLATE utf8mb4_unicode_ci, tanpa mengira default database. Menanda atau menanggalkan Unsigned pada satu key akan mengubah semua lajur foreign key yang merujuknya dalam edit yang sama, cukup satu langkah undo.

Ketidakpadanan yang sudah wujud, atau yang anda taip sendiri, akan dikesan oleh lint. Peraturan fk-type-mismatch-nya memaparkan “Jenis foreign key tidak sepadan dengan lajur yang dirujuknya” pada baris field foreign key, dengan butiran bahawa dua lajur itu berbeza dari segi UNSIGNED dan bahawa MySQL menolak foreign key yang lajur dan key-nya berbeza tanda. Pada MySQL, ia juga menyebut kedua-dua collation apabila key string dan lajur foreign key-nya berbeza character set atau collation.

Mengubah tanda pada key yang sudah dirujuk akan merancang urutan drop, modify, re-add dari bahagian di atas, dan analisis impak akan melaporkan setiap MODIFY sebelum ia dijalankan sebagai “Menulis semula users untuk menukar id” bersama bilangan baris, serta melaporkan perubahan tanda yang boleh menyebabkan kehilangan nilai, seperti BIGINT UNSIGNED kepada BIGINT, sebagai “nilai mungkin tidak bertahan selepas cast”. Foreign key yang ditambah semula juga disenaraikan, sebagai “Semakan key baharu menahan penulisan ke orders”, kerana itulah langkah paling perlahan dalam ujian di atas. Jika anda kemudian menukar SQL dalam rajah itu kepada PostgreSQL, SQL Server atau SQLite, integer unsigned akan dinaikkan satu saiz supaya semua nilai masih muat, dan BIGINT UNSIGNED tetap menjadi integer 64-bit signed (BIGINT, atau INTEGER pada SQLite) dengan nota bahawa nilai melebihi 2^63-1 tidak akan muat.

Key MySQL signed atau unsigned, dalam satu senarai

  • Skema sedia ada: guna tanda yang sudah ada pada primary keys-nya. Query information_schema di atas akan memberitahu anda.
  • Laravel atau WordPress: BIGINT UNSIGNED di semua tempat, termasuk lajur foreign key yang ditulis secara manual.
  • Rails, Django, atau skema baharu tanpa framework: signed BIGINT.
  • Lajur foreign key: mesti sama tepat dengan jenis, tanda dan saiz kunci. Ralat 3780 bermakna ia tidak sama.
  • Kalau anda tukar tanda kemudian: drop foreign keys, ubah suai kedua-dua belah, tambah semula keys, dan jangka setiap table disalin.

Pilihan yang sama pada PostgreSQL, yang tiada pilihan unsigned tetapi kunci boleh jadi UUID, ada dalam UUID vs bigint primary keys di Postgres. Penamaan constraints yang dicipta oleh foreign keys itu, supaya orders_user_id_foreign masih bermakna dalam lima tahun, ada dalam PostgreSQL constraint naming convention.

Sumber Rujukan:

  • Artikel asal: Dev.to Laravel
  • Diterjemah daripada artikel asal ke Bahasa Melayu oleh AI Editorial – haqis.com
Comments

Similar Posts