بررسی مفهومی SQL Mode در MariaDB و PostgreSQL

وقتی توسعه‌دهندگان برای نخستین بار با تنظیمی به نام SQL Mode در MariaDB یا MySQL مواجه می‌شوند، معمولاً آن را صرفاً یک گزینه پیکربندی ساده تصور می‌کنند. در ظاهر نیز چنین به نظر می‌رسد؛ چند مقدار متنی که هنگام اتصال به پایگاه داده یا در فایل تنظیمات سرور تعریف می‌شوند. اما واقعیت این است که اگر بخواهیم به این پرسش پاسخ دهیم که sqlmode چیست، باید آن را بسیار فراتر از یک تنظیم فنی بدانیم. SQL Mode در حقیقت بیانگر فلسفه و نگرش موتور پایگاه داده نسبت به اعتبار داده‌ها، استانداردهای SQL، نحوه گزارش خطاها و میزان سخت‌گیری سیستم در مواجهه با داده‌های نامعتبر است.

درک مفهوم SQL Mode زمانی اهمیت بیشتری پیدا می‌کند که بدانیم دو پایگاه داده محبوب یعنی MariaDB و PostgreSQL رویکردهای متفاوتی نسبت به این موضوع دارند. در MariaDB مفهوم مد های sql به صورت مستقیم و رسمی وجود دارد، در حالی که PostgreSQL اساساً فلسفه‌ای نزدیک به حالت سخت‌گیرانه را به صورت پیش‌فرض دنبال می‌کند و به همین دلیل چیزی کاملاً معادل SQL Mode در آن دیده نمی‌شود.

sqlmode چیست و چرا به وجود آمد؟

در سال‌های ابتدایی توسعه MySQL، یکی از اهداف اصلی این سیستم، سادگی و سازگاری با انواع برنامه‌ها بود. توسعه‌دهندگان می‌خواستند حتی اگر داده‌های ناقص یا نادرست وارد سیستم شوند، عملیات تا حد امکان ادامه پیدا کند و برنامه متوقف نشود.

برای مثال اگر ستونی از نوع عددی تعریف شده بود اما کاربر رشته‌ای نامعتبر را وارد می‌کرد، موتور پایگاه داده تلاش می‌کرد مقدار را به نزدیک‌ترین حالت معتبر تبدیل کند. در بسیاری از موارد به جای ایجاد خطا، تنها یک Warning ثبت می‌شد و عملیات ادامه پیدا می‌کرد.

این رفتار اگرچه برای برخی کاربردهای ساده مفید بود، اما در سامانه‌های حرفه‌ای و سازمانی مشکلات بزرگی ایجاد می‌کرد. داده‌های اشتباه وارد پایگاه داده می‌شدند و توسعه‌دهندگان هفته‌ها یا ماه‌ها بعد متوجه فساد داده‌ها می‌شدند.

در همین نقطه مفهوم SQL Mode متولد شد. هدف این مکانیزم آن بود که مدیر پایگاه داده مشخص کند سیستم تا چه اندازه سخت‌گیر یا انعطاف‌پذیر باشد.

بنابراین وقتی می‌پرسیم sqlmode چیست، در واقع درباره مجموعه قوانینی صحبت می‌کنیم که رفتار موتور پایگاه داده را هنگام اجرای دستورات SQL تعریف می‌کنند.

SQL Mode در MariaDB چگونه عمل می‌کند؟

در MariaDB، SQL Mode نوعی لایه رفتاری است که بر روی موتور SQL قرار می‌گیرد. این لایه تعیین می‌کند هنگام مواجهه با شرایط خاص چه اتفاقی رخ دهد.

برای مثال ممکن است یک توسعه‌دهنده بخواهد هرگونه مقدار نامعتبر باعث خطا شود. توسعه‌دهنده دیگری ممکن است ترجیح دهد داده‌ها تا حد امکان پذیرفته شوند و تنها هشدار ثبت گردد.

به همین دلیل MariaDB مجموعه بزرگی از مد های sql را ارائه می‌دهد که می‌توانند به صورت مستقل یا ترکیبی فعال شوند.

نکته مهم این است که SQL Mode ساختار جداول را تغییر نمی‌دهد. همچنین نوع داده‌ها را نیز تغییر نمی‌دهد. آنچه تغییر می‌کند نحوه تفسیر و اعتبارسنجی داده‌ها است.

به بیان دیگر SQL Mode بیشتر به شخصیت و رفتار پایگاه داده مربوط می‌شود تا ساختار فیزیکی آن.

مفهوم سخت‌گیری در پایگاه داده

یکی از مهم‌ترین مفاهیمی که برای درک SQL Mode باید شناخت، مفهوم Strictness یا سخت‌گیری است.

فرض کنید ستونی برای ذخیره سن افراد تعریف کرده‌اید. اگر کاربر مقدار «علی» را در این ستون وارد کند چه اتفاقی باید رخ دهد؟

پاسخ به این سؤال کاملاً وابسته به SQL Mode است.

در حالت‌های قدیمی و غیرسخت‌گیر، موتور پایگاه داده ممکن است مقدار را به صفر تبدیل کند و عملیات را ادامه دهد.

اما در حالت‌های سخت‌گیرانه مانند STRICT_TRANS_TABLES، سیستم بلافاصله خطا ایجاد می‌کند و از ثبت داده جلوگیری می‌شود.

در نتیجه مهم‌ترین نقش مد های sql کنترل میزان سخت‌گیری سیستم نسبت به داده‌های نامعتبر است.

چرا STRICT_TRANS_TABLES محبوب‌ترین SQL Mode است؟

امروزه تقریباً تمام پروژه‌های حرفه‌ای MariaDB و MySQL از مد STRICT_TRANS_TABLES استفاده می‌کنند.

دلیل این محبوبیت آن است که این حالت تعادلی مناسب میان انعطاف‌پذیری و امنیت داده ایجاد می‌کند.

در این حالت، موتور پایگاه داده تلاش نمی‌کند داده‌های نامعتبر را حدس بزند یا اصلاح کند. در عوض خطا را به برنامه بازمی‌گرداند تا توسعه‌دهنده تصمیم مناسب را بگیرد.

در فریم‌ورک‌هایی مانند Peewee نیز معمولاً مشاهده می‌کنیم که هنگام ایجاد اتصال به MariaDB، گزینه sql_mode روی STRICT_TRANS_TABLES تنظیم می‌شود. هدف از این کار اطمینان از کیفیت داده‌های ذخیره شده است.

همین رویکرد در SQLAlchemy نیز قابل پیاده‌سازی است. هرچند SQLAlchemy مستقیماً مفهومی به نام SQL Mode ندارد، اما می‌توان هنگام برقراری اتصال، تنظیمات مربوط به MariaDB یا MySQL را اعمال کرد.

بنابراین چه از Peewee استفاده شود و چه از SQLAlchemy، مفهوم اصلی تغییری نمی‌کند؛ هدف جلوگیری از ورود داده‌های ناسالم به سیستم است.

STRICT_TRANS_TABLES یعنی چه؟

بطور کلی این مد باعث می‌شود MySQL هنگام درج یا آپدیت داده‌های نامعتبر سخت‌گیرتر رفتار کند. مثلا اطلاعات افرادی را در جدول Users دارید و ستون age را از نوع INT بصورت NOTNULL تعریف کرده اید.

حالا شخصی میخواهد مقدار نادرست وارد کند:

INSERT INTO Users(age)
VALUES (‘abc’);

اگر مد STRICT_TRANS_TABLES فعال نباشد عموما دیتابیس خطا نمیدهد و بجای abc مقدار 0 را در age جاگذاری میکند که غیر استاندارد و در بعضی مواقع ناامن است پس با این مد شما به دیتابیس میگویید که باید در صورت مواجه با چنین شرایطی خطا داشته باشی و تراکنش را بطور کامل لغو کنی!

مد های sql و مسئله استاندارد SQL

بخش مهم دیگری از فلسفه SQL Mode به استاندارد SQL مربوط می‌شود.

تاریخچه MySQL نشان می‌دهد که این پایگاه داده در بسیاری از نسخه‌های اولیه خود از رفتارهای غیر استاندارد پشتیبانی می‌کرد. این رفتارها باعث می‌شد مهاجرت به سایر سیستم‌های مدیریت پایگاه داده دشوار شود.

برخی از مد های sql دقیقاً برای نزدیک‌تر کردن رفتار MariaDB به استاندارد ANSI SQL طراحی شده‌اند.

برای مثال مد ONLY_FULL_GROUP_BY تضمین می‌کند که دستورات GROUP BY مطابق قواعد استاندارد SQL اجرا شوند.

بدون این حالت، ممکن است یک کوئری نتایج مبهم یا غیرقابل پیش‌بینی تولید کند. اما با فعال شدن این مد، سیستم از همان ابتدا جلوی چنین خطاهایی را می‌گیرد.

در نتیجه SQL Mode فقط درباره اعتبار داده‌ها نیست؛ بلکه درباره سازگاری با استانداردهای صنعت نیز هست.

PostgreSQL و نگاه متفاوت به SQL Mode

وقتی به PostgreSQL می‌رسیم، وضعیت کاملاً متفاوت می‌شود.

در PostgreSQL چیزی با نام رسمی SQL Mode وجود ندارد. دلیل این موضوع آن است که بسیاری از رفتارهایی که در MariaDB از طریق SQL Mode فعال می‌شوند، در PostgreSQL از ابتدا به عنوان رفتار پیش‌فرض سیستم در نظر گرفته شده‌اند.

برای مثال PostgreSQL به طور ذاتی نسبت به نوع داده‌ها بسیار سخت‌گیر است. اگر مقدار نامعتبری برای یک ستون ارسال شود، معمولاً بلافاصله خطا ایجاد می‌شود.

همچنین PostgreSQL در اجرای بسیاری از قواعد استاندارد SQL سخت‌گیری بیشتری نسبت به MySQL و MariaDB دارد.

به همین دلیل توسعه‌دهندگانی که از MariaDB به PostgreSQL مهاجرت می‌کنند، معمولاً احساس می‌کنند PostgreSQL همیشه در یک حالت STRICT قرار دارد.

از دیدگاه مفهومی می‌توان گفت PostgreSQL فلسفه SQL Mode را در طراحی هسته خود ادغام کرده است، در حالی که MariaDB این قابلیت را به صورت مجموعه‌ای از تنظیمات اختیاری ارائه می‌دهد.

SQL Mode و کیفیت داده‌ها

یکی از مهم‌ترین دلایل استفاده از SQL Mode حفظ کیفیت داده‌ها است.

بسیاری از مشکلات سامانه‌های بزرگ نه به دلیل باگ‌های برنامه‌نویسی، بلکه به علت داده‌های ناسالم رخ می‌دهند.

وقتی داده اشتباه وارد پایگاه داده شود، ممکن است ماه‌ها بعد گزارش‌های مالی، تحلیل‌های آماری یا فرآیندهای تجاری را دچار اختلال کند.

SQL Mode نقش اولین خط دفاعی را ایفا می‌کند. پیش از آنکه داده به جدول برسد، اعتبار آن بررسی می‌شود.

در نتیجه پاسخ عملی به پرسش sqlmode چیست این است که SQL Mode مکانیزمی برای حفاظت از یکپارچگی و اعتبار داده‌ها در سطح پایگاه داده است.

ارتباط SQL Mode با ORM ها

گاهی تصور می‌شود SQL Mode بخشی از ORM هایی مانند Peewee یا SQLAlchemy است. این تصور نادرست است.

ORM تنها واسطه‌ای برای ارتباط برنامه با پایگاه داده محسوب می‌شود.

زمانی که در Peewee پارامتر sql_mode تعریف می‌شود، در واقع ORM در حال ارسال یک دستور تنظیم به MariaDB است.

در SQLAlchemy نیز داستان مشابهی وجود دارد. SQLAlchemy SQL Mode جدیدی تعریف نمی‌کند، بلکه تنظیمات موتور پایگاه داده را مدیریت می‌کند.

بنابراین SQL Mode همواره متعلق به موتور پایگاه داده است و نه به لایه ORM.

مثال استفاده از SQLMode در Peewee:

db = MySQLDatabase(
“mydb”,
user=“root”,
password=“…”,
sql_mode=“STRICT_TRANS_TABLES”,
)

مثال استفاده از SQLMode در SQLALCHEMY :

mysql+pymysql://user:pass@host/db?init_command=SET%20sql_mode='STRICT_TRANS_TABLES'

آینده SQL Mode در پایگاه داده‌ها

روند توسعه پایگاه داده‌ها نشان می‌دهد که صنعت نرم‌افزار به سمت سخت‌گیری بیشتر حرکت می‌کند. در گذشته انعطاف‌پذیری اولویت اصلی بود، اما امروز صحت داده‌ها اهمیت بیشتری دارد.

به همین دلیل نسخه‌های جدید MariaDB و MySQL به طور پیش‌فرض بسیاری از مد های sql سخت‌گیرانه را فعال می‌کنند. رفتارهای قدیمی و سهل‌گیرانه به تدریج کنار گذاشته شده‌اند.

این روند تا حد زیادی تحت تأثیر موفقیت PostgreSQL بوده است؛ پایگاه داده‌ای که از ابتدا بر صحت داده‌ها و رعایت استانداردها تأکید داشت.

جمع‌بندی

اگر بخواهیم در یک جمله توضیح دهیم که sqlmode چیست، باید بگوییم SQL Mode مجموعه‌ای از قوانین رفتاری است که تعیین می‌کند موتور پایگاه داده هنگام پردازش دستورات SQL، اعتبارسنجی داده‌ها و مدیریت خطاها چگونه عمل کند.

در MariaDB، مد های sql ابزارهایی هستند که میزان سخت‌گیری سیستم، نحوه برخورد با داده‌های نامعتبر و میزان پایبندی به استاندارد SQL را کنترل می‌کنند. مشهورترین آن‌ها STRICT_TRANS_TABLES است که امروزه به استاندارد عملی بسیاری از پروژه‌های حرفه‌ای تبدیل شده است.

در مقابل، PostgreSQL رویکرد متفاوتی دارد و بسیاری از این رفتارهای سخت‌گیرانه را به صورت پیش‌فرض در هسته خود پیاده‌سازی کرده است. به همین دلیل مفهومی مستقل با نام SQL Mode در آن دیده نمی‌شود.

در نهایت، درک صحیح SQL Mode تنها یک موضوع پیکربندی نیست؛ بلکه بخشی از فهم عمیق‌تر فلسفه طراحی پایگاه داده‌ها، مدیریت کیفیت داده و تضمین صحت اطلاعات در سامانه‌های نرم‌افزاری مدرن محسوب می‌شود.