تم إعداد هذا المقال لطلاب الدورة التدريبية "MS SQL Server Developer"
أريد أن أشارك قصة من أحد المشاريع السابقة ، والتي توضح أنه يجب اختيار الترتيب بعناية فائقة. وحول ما يحدث إذا تم اختيار هذه المعلمة بشكل غير صحيح ، وما هي الخيارات الموجودة لحل المشكلة.
أولا ، مقدمة صغيرة حول ما هو الترتيب. في SQL Server ، تخبر المعلمة Collation الخادم كيفية فرز ومقارنة الصفوف. على سبيل المثال ، خطوط "Apple" و "apple". هل هي مختلفة أم لا؟ ذلك يعتمد على الترتيب المحدد. إذا أصبح السجل أقل أو أقل وضوحًا ، فما العمل مع مثال "شجرة عيد الميلاد" و "شجرة عيد الميلاد"؟ عد لهم كما هو نفسه أو مختلفة؟ هذا هو كل شيء في الترتيب أيضا.
حدثت القصة في مشروع تشبه وظيفته إلى حد كبير DropBox أو Google Drive. يوفر القدرة على إدارة المجلدات والملفات المتزامنة الخاصة بهم على أجهزة مختلفة ، بالإضافة إلى قدرة المستخدمين الآخرين على الوصول إلى هذا المجلد المتزامن.
لذلك ، بدأت القصة بحقيقة أنه على خوادم Prod كانت هناك 75-90 ٪ من الأخطاء في السجلات (انظر لقطة الشاشة أدناه) ، وليس من الواضح من أين أتوا ، وما سبب ذلك. الخطأ هو: "ReadWrtLst لم يكتمل". بعد ذلك جاءت تفاصيل المستخدم ومجلداته.
عثرت الشفرة بسرعة على مكان أدى إلى حدوث خطأ ، لكن لم نتمكن من فهم سبب حدوثه وكيفية إعادة إنتاجه ، وكان من الواضح فقط أن الخطأ مرتبط بطريقة أو بأخرى بحقيقة أن المستخدم تمكن بطريقة ما من قم بإنشاء مجلد آخر بنفس الاسم في نظام التشغيل لديك.
لقد جمعنا معلومات عن المستخدمين الذين صدر لهم هذا الخطأ. وهنا واجهنا المفاجأة الأولى: من بين ملايين مستخدمي النظام ، كان 50 فقط منهم قد حصلوا على هذا الخطأ ، وهؤلاء المستخدمين الـ 50 يولدون 90٪ من سجلات الأخطاء. نظرًا لعدم إمكانية إعادة إنتاج الموقف ، قررنا الاتصال بأحد المستخدمين ومعرفة سبب عدم مزامنة أحد المجلدات معه. كان المجلد يبدو لنا كما هو الحال مع الآخرين ، والفرق الوحيد هو أنه تم استدعاؤه بلغة المستخدم باستخدام الحروف الهيروغليفية. وكان المستخدم الياباني. بالمناسبة ، من بين هؤلاء المستخدمين الـ 50 ، كان اليابانيون هم الأغلبية.
بفضل أحد مطوري الفريق ، تمكنا من إعادة إنتاج الخطأ. الخطأ هو أن نظام التشغيل اعتبر أسماء المجلدات مختلفة ، واعتبرها SQL Server هي نفسها بسبب الترتيب المحدد.
الترتيب المستخدم في المشروع:
SQL_Latin1_General_CP1_CI_AS
استطرادا صغيرا حول كيفية قراءة الترتيب. (إذا كنت معتادًا عليها ، فلا تتردد في تخطيها.)
لذلك ، هناك عدة أجزاء لـ Collation:
- SQL - خيارات الفرز حسب SQL Server (SQL في بداية Collation) أو Windows (عندها ستكون Latin1_ ...) فقط ؛
- Latin1_General - اللغة أو اللغة المستخدمة ؛
- CP1 - صفحة الرموز - صفحة الرموز ؛
- CI - Case Insensitive - Case insensitive؛
- AS - لهجة حساسة - مع الأخذ في الاعتبار محاور عصبية أو علامات التشكيل ، وبعبارة أخرى ، لا تعتبر "أ" مساوية لـ "ấ"
كان هذا الترتيب مرة واحدة في الترتيب الافتراضي عند تثبيت SQL Server.
ما هي الخيارات المتاحة؟
- _KS - مع الأخذ في الاعتبار الأحرف اليابانية من هيراغانا وكاتاكانا ، إذا لم يتم تحديد الخيار ، فسيقوم SQL Server بترجمة الهيروغليفية من هيراغانا وكاتاكانا على النحو نفسه.
- _WS - مع الأخذ في الاعتبار عرض الأحرف ، إذا لم يتم تحديد المعلمة ، فسيتم اعتبار "النص" و "T ext" نفس السطور.
- _VSS - مع الأخذ في الاعتبار علامات اختيار خيار الهجاء باللغة اليابانية ، ظهرت من الإصدار 2017.
- _UTF8 - يسمح لك بتخزين البيانات في UTF8.
تستخدم جميع حقول النص في قاعدة البيانات نوع NVARCHAR.
اتضح أنه نظرًا لتجاهل الترتيب الحالي الفرق في تهجئة الأحرف اليابانية والاختلاف في عرض الأحرف ، لم يقارن SQL Server السلاسل بالطريقة نفسها التي فعل بها نظام التشغيل ، مما تسبب في المشكلة ، أي يمكن للمستخدم إنشاء مجلدات ، لا يمكن إضافتها إلى النظام للمزامنة. نفس الشيء سيحدث لاحقًا عند مقارنة أسماء الملفات.
بدأنا في التفكير في كيفية حل هذه المشكلة وتغيير الترتيب.
يمكن ضبط الترتيب على عدة مستويات:
- خادم SQL مثيل
- قاعدة بيانات
- طاولة
- المجال
في الوقت نفسه ، لا يُنصح بإجراء ترتيب نسخ مختلف داخل قاعدة البيانات ، لأنه في كل مرة عند مقارنة الصفوف مع ترتيب نسخ مختلف ، ستحتاج إلى إجراء التحويل باستخدام COLLATE ، مع الإشارة إلى الخادم بترتيب المقارنة الذي يجب استخدامه.
ما هي الخيارات الموجودة في الموقف حيث يكون من الواضح أن الترتيب غير محدد بشكل صحيح؟
- تغيير الترتيب على مستوى الديسيبل ؛
- تغيير الترتيب على مستوى الحقل (في حالتنا ، لم تكن هناك فائدة في التغيير للجدول بأكمله) ؛
- أضف حقل Varbinary ، الذي تكتب فيه نسخة مكررة من الحقل باسم المجلد ، واستخدمها للمقارنة ؛
- أخبر المستخدمين أن هناك قيودًا على دعم الأحرف في أسماء الدليل.
الخيار الأول - تغيير الترتيب على مستوى قاعدة البيانات - هو الأصعب. في حالة قاعدة البيانات ، سيكون من الضروري إعادة إنشاء قاعدة البيانات وإعادة تحميل البيانات هناك. نظرًا لأن النظام يعمل 24/7 ، تم رفض هذا الخيار على الفور.
الخيار الثاني حول تغيير الحقل: أسهل خيار لتنفيذه هو إضافة حقل مع الترتيب المرغوب فيه ونقل البيانات هناك. ولكن بعد ذلك سيكون من الضروري تغيير التعليمات البرمجية في قاعدة البيانات التي تعمل مع هذا الحقل ، وكان هناك الكثير من التعليمات البرمجية في قاعدة البيانات.
لقد أحببنا الخيار الثالث أكثر من غيره ، لأنه من الناحية النظرية قام بأقل التغييرات ، نظرًا لأن الحقل الرئيسي سيظل موجودًا مع الترتيب الحالي ، ولن نواجه مشاكل مع تحويله ، في حين أن جميع الوظائف اللازمة في شكل محاسبة الأبجدية اليابانية أو واسعة سوف الشخصيات العمل. الجانب السلبي هو أنه كان من الضروري إجراء تغييرات على جزء البرنامج ، ولكن بما أن جزء الخادم هذا ، فيمكن القيام بذلك.
كان الخيار الرابع هو الأكثر بساطة في هذه الحالة ، لأن إجمالي عدد المستخدمين كان عدة ملايين ، وكان 50 فقط لديهم مشكلة ، ولكن إذا كان التطبيق مستخدمًا بنشاط في اليابان ، فإن هذا الحل لن يكون ذا فائدة تذكر.
بعد تقديم البيانات إلى الإدارة ، تقرر إخطار المستخدمين بأن البرنامج لا يدعم عددًا من الأحرف ، وعند استخدامه في الملفات والمجلدات المتزامنة ، قد لا يعمل البرنامج بشكل صحيح. هذا حل مؤقت ، لأنه مع مزيد من التوزيع ، سيزداد عدد المستخدمين الذين يواجهون مشكلة مماثلة ، وسيكون من الضروري تغيير شيء ما باستخدام الخيارات الثلاثة الأولى.
يعتمد الخيار الأفضل لاختيار Collation على متطلبات التطبيق الخاص بك. إذا كنت تريد أن يقوم SQL Server بمقارنة السلاسل بنفس طريقة نظام التشغيل ، فمن المؤكد أن الترتيب غير صحيح افتراضيًا. لسوء الحظ ، نادراً ما تظهر مثل هذه الفروق الدقيقة في بداية المشروع عند تصميم نظام ، لكن ، على أمل ، بعد قراءة المقال ، سوف تتذكر الموقف الموصوف ولا تتدخل في مثل هذه المشكلة.
موارد الترتيب مفيدة:
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support؟view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collations-shy-ask/
https://www.virtual-dba.com/sql-server-collation/