ما هو أفضل ترتيب لاستخدامه مع MySQL مع PHP؟


Answers

كن على وعي تام بهذه المشكلة التي يمكن أن تحدث عند استخدام utf8_general_ci .

لن يميز MySQL بين بعض الأحرف في عبارات محددة ، إذا تم استخدام ترتيب utf8_general_ci . هذا يمكن أن يؤدي إلى أخطاء سيئة للغاية - خاصة على سبيل المثال ، حيث تشارك أسماء المستخدمين. استنادًا إلى التطبيق الذي يستخدم جداول قاعدة البيانات ، قد تسمح هذه المشكلة للمستخدمين الضارين بإنشاء اسم مستخدم يطابق حساب مسؤول.

تعرض هذه المشكلة نفسها على الأقل في الإصدارات 5.x الأولى - لست متأكداً إذا كان هذا السلوك قد تغير لاحقاً.

أنا لا DBA ، ولكن لتجنب هذه المشكلة ، وأنا أذهب دائما مع utf8-bin بدلا من واحد غير حساس لحالة الأحرف.

يصف البرنامج النصي أدناه المشكلة حسب المثال.

-- first, create a sandbox to play in
CREATE DATABASE `sandbox`;
use `sandbox`;

-- next, make sure that your client connection is of the same 
-- character/collate type as the one we're going to test next:
charset utf8 collate utf8_general_ci

-- now, create the table and fill it with values
CREATE TABLE `test` (`key` VARCHAR(16), `value` VARCHAR(16) )
    CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `test` VALUES ('Key ONE', 'value'), ('Key TWO', 'valúe');

-- (verify)
SELECT * FROM `test`;

-- now, expose the problem/bug:
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get BOTH keys here! MySQLs UTF8 collates that are 
-- case insensitive (ending with _ci) do not distinguish between 
-- both values!
--
-- collate 'utf8_bin' doesn't have this problem, as I'll show next:
--

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Note that we get just one key now, as you'd expect.
--
-- This problem appears to be specific to utf8. Next, I'll try to 
-- do the same with the 'latin1' charset:
--

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_general_ci

-- next, convert the values that we've previously inserted
-- in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected). This shows 
-- that the problem with utf8/utf8_generic_ci isn't present 
-- in latin1/latin1_general_ci
--
-- To complete the example, I'll check with the binary collate
-- of latin1 as well:

-- first, reset the client connection charset/collate type
charset latin1 collate latin1_bin

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET latin1 COLLATE latin1_bin;

-- now, re-check for the bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Again, only one key is returned (expected).
--
-- Finally, I'll re-introduce the problem in the exact same 
-- way (for any sceptics out there):

-- first, reset the client connection charset/collate type
charset utf8 collate utf8_generic_ci

-- next, convert the values that we've previously inserted in the table
ALTER TABLE `test` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- now, re-check for the problem/bug
SELECT * FROM test WHERE `value` = 'value';

--
-- Two keys.
--

DROP DATABASE sandbox;
Question

أتساءل ما إذا كان هناك خيار "أفضل" للتجميع في MySQL لموقع عام حيث لا تكون متأكدًا بنسبة 100٪ مما سيتم إدخاله؟ أفهم أن جميع الترميزات يجب أن تكون هي نفسها ، مثل MySQL و Apache و HTML وأي شيء داخل PHP.

في الماضي ، قمت بإعداد PHP للإخراج "UTF-8" ، لكن أي ترتيب يقوم بهذا الترتيب في MySQL؟ أفكر في أنها واحدة من UTF-8 ، لكنني استخدمت utf8_unicode_ci و utf8_general_ci و utf8_bin قبل.







بشكل أساسي ، يعتمد الأمر على طريقة تفكيرك في السلسلة.

أنا دائما استخدام utf8_bin بسبب المشكلة التي أبرزتها جوس. في رأيي ، بقدر ما ينبغي أن تكون قاعدة البيانات المعنية ، سلسلة ما زالت مجرد سلسلة. السلسلة عبارة عن عدد من أحرف UTF-8. الحرف له تمثيل ثنائي ، فلماذا يحتاج إلى معرفة اللغة التي تستخدمها؟ عادة ، سيقوم الناس ببناء قواعد بيانات للأنظمة مع نطاق المواقع متعددة اللغات. هذا هو بيت القصيد من استخدام UTF-8 كمجموعة أحرف. أنا من أؤلئك المحترفين ، لكنني أعتقد أن مخاطر الأخطاء تفوق بشكل كبير الميزة الطفيفة التي قد تحصل عليها في الفهرسة. يجب أن تتم أي قواعد ذات صلة باللغة على مستوى أعلى بكثير من نظام إدارة قواعد البيانات (DBMS).

في كتابي "القيمة" لا ينبغي أبدا في المليون سنة أن تكون مساوية ل "valúe".

إذا كنت أرغب في تخزين حقل النص وإجراء بحث غير حساس لحالة الأحرف ، فسوف أستخدم دالات سلسلة MYSQL مع وظائف PHP مثل LOWER () ودالة php strtolower ().




الجواب المقبول إلى حد ما يقترح استخدام utf8_unicode_ci ، وفي حين أن مشاريع جديدة كبيرة ، كنت أرغب في ربط تجربتي العكسية الأخيرة فقط في حالة أنها توفر أي شخص في وقت ما.

لأن utf8_general_ci هو الترتيب الافتراضي لـ Unicode في MySQL ، إذا كنت تريد استخدام utf8_unicode_ci ، فسينتهي بك الحال إلى تحديده في الكثير من الأماكن.

على سبيل المثال ، لا تحتوي جميع اتصالات العميل فقط على مجموعة أحرف افتراضية (منطقية بالنسبة إلي) ولكن أيضًا ترتيب نسخ افتراضي (أي أن الترتيب سيظل دائمًا افتراضيًا لـ utf8_general_ci for unicode).

من المحتمل أنك إذا استخدمت utf8_unicode_ci لحقولك ، فسيكون من الضروري تحديث النصوص البرمجية التي تتصل بقاعدة البيانات لذكر الترتيب المطلوب بشكل صريح - وإلا قد تفشل طلبات البحث باستخدام السلاسل النصية عندما يستخدم الاتصال الخاص بك الترتيب الافتراضي.

والنتيجة هي أنه عند تحويل نظام قائم من أي حجم إلى Unicode / utf8 ، فقد ينتهي بك الأمر لاستخدام utf8_general_ci بسبب الطريقة التي تعالج بها MySQL الإعدادات الافتراضية.




فمن الأفضل استخدام مجموعة الأحرف utf8mb4 مع ترتيب utf8mb4_unicode_ci .

مجموعة الأحرف ، utf8 ، تدعم فقط كمية صغيرة من نقاط رمز UTF-8 ، حوالي 6٪ من الأحرف الممكنة. utf8 يدعم فقط Basic Multilingual Plane (BMP). هناك 16 طائرة أخرى. كل طائرة تحتوي على 65،536 حرفا. utf8mb4 يدعم جميع الطائرات ال 17.

سيقوم MySQL باقتطاع 4 أحرف UTF-8 مما يؤدي إلى تلف البيانات.

تم تقديم مجموعة الأحرف utf8mb4 في MySQL 5.5.3 في 2010-03-24.

بعض التغييرات المطلوبة لاستخدام مجموعة الأحرف الجديدة ليست بسيطة:

  • قد يلزم إجراء التغييرات في محول قاعدة بيانات التطبيق.
  • يجب إجراء التغييرات على my.cnf ، بما في ذلك تعيين مجموعة الأحرف ، وترتيب وتبديل innodb_file_format إلى Barracuda
  • قد تحتاج إلى عبارات SQL CREATE لتضمين: ROW_FORMAT=DYNAMIC
    • مطلوب DYNAMIC لفهرسة VARCHAR (192) وأكبر.

ملاحظة: قد يتطلب التبديل إلى Barracuda من Antelope إعادة تشغيل خدمة MySQL أكثر من مرة. لا يتغير innodb_file_format_max إلا بعد إعادة تشغيل خدمة MySQL إلى: innodb_file_format = barracuda .

يستخدم MySQL تنسيق ملف Antelope InnoDB القديم. يدعم Barracuda تنسيقات Barracuda ديناميكية ، والتي ستحتاج إليها إذا كنت لا ترغب في ضرب أخطاء SQL لإنشاء فهارس ومفاتيح بعد التبديل إلى charset: utf8mb4

  • # 1709 - حجم عمود الفهرس كبير جدًا. الحد الأقصى لحجم العمود هو 767 بايت.
  • # 1071 - المفتاح المحدد طويل جدًا ؛ أقصى طول المفتاح هو 767 بايت

تم اختبار السيناريو التالي على MySQL 5.6.17: بشكل افتراضي ، يتم تكوين MySQL على النحو التالي:

SHOW VARIABLES;

innodb_large_prefix = OFF
innodb_file_format = Antelope

أوقف خدمة MySQL وأضف الخيارات إلى my.cnf الموجودة لديك:

[client]
default-character-set= utf8mb4

[mysqld]
explicit_defaults_for_timestamp = true
innodb_large_prefix = true
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = true

# Character collation
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

مثال على عبارة SQL CREATE:

CREATE TABLE Contacts (
 id INT AUTO_INCREMENT NOT NULL,
 ownerId INT DEFAULT NULL,
 created timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 contact VARCHAR(640) NOT NULL,
 prefix VARCHAR(128) NOT NULL,
 first VARCHAR(128) NOT NULL,
 middle VARCHAR(128) NOT NULL,
 last VARCHAR(128) NOT NULL,
 suffix VARCHAR(128) NOT NULL,
 notes MEDIUMTEXT NOT NULL,
 INDEX IDX_CA367725E05EFD25 (ownerId),
 INDEX created (created),
 INDEX modified_idx (modified),
 INDEX contact_idx (contact),
 PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT=DYNAMIC;
  • يمكنك رؤية الخطأ # 1709 الذي تم إنشاؤه لـ INDEX contact_idx (contact) إذا تمت إزالة ROW_FORMAT=DYNAMIC من عبارة CREATE.

ملاحظة: تغيير الفهرس للحد إلى 128 حرفًا على contact يلغي الحاجة إلى استخدام Barracuda مع ROW_FORMAT=DYNAMIC

INDEX contact_idx (contact(128)),

لاحظ أيضا: عندما تقول حجم الحقل هو VARCHAR(128) ، وهذا ليس 128 بايت. يمكنك استخدام 128 حرفًا أو 4 بايت أو 128 حرفًا واحدًا.

يجب أن تحتوي عبارة INSERT هذه على حرف "poo" ذو 4 بايت في الصف 2:

INSERT INTO `Contacts` (`id`, `ownerId`, `created`, `modified`, `contact`, `prefix`, `first`, `middle`, `last`, `suffix`, `notes`) VALUES
(1, NULL, '0000-00-00 00:00:00', '2014-08-25 03:00:36', '1234567890', '12345678901234567890', '1234567890123456789012345678901234567890', '1234567890123456789012345678901234567890', '12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678', '', ''),
(2, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', ''),
(3, NULL, '0000-00-00 00:00:00', '2014-08-25 03:05:57', 'poo', '12345678901234567890', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '123💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩💩', '', '');

يمكنك رؤية مقدار المساحة المستخدمة بواسطة العمود last :

mysql> SELECT BIT_LENGTH(`last`), CHAR_LENGTH(`last`) FROM `Contacts`;
+--------------------+---------------------+
| BIT_LENGTH(`last`) | CHAR_LENGTH(`last`) |
+--------------------+---------------------+
|               1024 |                 128 | -- All characters are ASCII
|               4096 |                 128 | -- All characters are 4 bytes
|               4024 |                 128 | -- 3 characters are ASCII, 125 are 4 bytes
+--------------------+---------------------+

في محول قاعدة البيانات ، قد ترغب في تعيين مجموعة الأحرف وترتيبها لاتصالك:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

في PHP ، سيتم تعيين هذا على: \PDO::MYSQL_ATTR_INIT_COMMAND

المراجع: