آموزش انواع داده در MySQL

همواره در طراحی یک وب اپلیشیکن و یا نرم‌افزار تحت موبایل و دسکتاپ، طراحی پایگاه‌داده و مهم‌تر از آن انتخاب نوع‌داده امری مهم و ضروری تلقی می‌شود. بگونه‌ای که بسیاری از توسعه‌دهنده‌ها دغدغه‌ی اصلی‌شان طراحی مدرن و کارآمد یک پایگاه‌داده برای وب اپلیکیشن یا نرم‌افزارشان است. بنابراین لازم دانستیم یک فصل کاملا مجزا به انواع داده در پایگاه‌داده MySQL‌ اختصاص دهیم. درنظر داشته باشید که این انواع داده گاها در برخی دیتابیس‌ها مانند SQL Server نیز یکسان است و تنها تفاوت در برخی اسامی است.

در اینجا داده‌ها را به چند دسته‌ی کلی داده‌های متنی، عددی، تاریخی تقسیم‌بندی کرده ایم تا تشخیص هر مجموعه به سادگی صورت پذیرد:

داده‌های متنی

CHAR(size)

این نوع داده برای ذخیره‌ی رشته‌ای با طول ۰ تا ۲۵۵ کاراکتر به کار گرفته می‌شود که شامل حروف، اعداد و کاراکترهای مجاز است. فضایی که این نوع‌داده در دیتابیس اشغال می‌کند برابر است با ۲۵۵ بایت. ( البته اصل فرمول برای CHAR(m) داده برابر است با m*w که در این عبارت w حداکثر طول کاراکتری‌ست که متناسب با ساختار جداول مشخص می‌شود.)

VARCHAR(size)

این نوع داده برای ذخیره‌ی رشته‌ای با طول ۰ تا ۶۵,۳۵۳ کاراکتر به کار گرفته می‌شود که شامل حروف، اعداد و کاراکترهای مجاز است. عددی که درون این نوع داده قرار می‌گیرد به عنوان تعداد کاراکتر درنظر گرفته می‌شود. طول این نوع داده متغییر است.

همچنین فضایی که نوع داده‌ی VARCHAR اشغال می‌کند تا ۲۵۵ کاراکتر برابر است با VARCHAR(K)+1 یعنی مثلا اگر داده‌ی رشته‌ای ما ۳۰ کاراکتر داشته باشد فضایی که در دیتابیس اشغال می‌کند برابر است با ۳۱ = ۱+۳۰ بایت. و اگر بیش از ۲۵۵ کاراکتر باشد برابر است با VARCHAR(K)+2 که در این حالت مثلا اگر داده‌ی رشته‌ای ما ۲۶۰ کاراکتر باشد آنگاه فضایی که اشغال می‌کند برابر است با ۲۶۲ = ۲ + ۲۶۰ بایت است.

تفاوت بین CHAR و VARCHAR

مثال: CHAR(50), VARCHAR(50)

در دو عبارت فوق مقدار ۵۰ کاراکتر در هر یک از متغییرها ذخیره می‌شود تنها تفاوتی که وجود دارد این است:

در CHAR مقدار ۵۰ کاراکتر به همراه ۱۷۵ = ۵۰-۲۵۵ فضای خالی (space) قرار می‌گیرد. یعنی char تمام ۲۵۵ کاراکتر را ذخیره می‌کند و هر مقداری که درون آرگومان آن قرار بگیرد را کاراکتر و مقادیر باقی‌مانده را با فضای خالی space پر می‌کند. و در نهایت فضایی که ذخیره می‌کند برابر است با ۲۵۵ بایت.

اما در VARCHAR دقیقا مقدار ۵۰ کاراکتر ذخیره می‌شود و آنچه که در دیتابیس قرار می‌گیرد از نظر حجمی کمتر از مقدار موجود در CHAR می‌باشد. بنابراین مقدار حجمی که ذخیره می‌شود برابر است با:‌ ۵۱ = ۱ + ۵۰ بایت. یعنی میزان فضایی که در دیتابیس اشغال می‌کند برابر ۵۱ بایت است.

یک سوال: بهتر است از کدام نوع داده استفاده کنیم؟
پاسخ: در صورتیکه پردازش و سرعت پایگاه‌داده در پاسخ به درخواست شما مهم است از CHAR استفاده کنید و در صورتیکه میزان فضای اشغالی برای شما مهم‌تر است از VARCHAR. اما در هر صورت CHAR از نوع استاتیک است درحالیکه VARCHAR از نوع دینامیک می‌باشد بنابراین CHAR از نظر سرعت پردازشی ۵۰٪ سریع‌تر می‌باشد. نکته‌ی دیگری که قابل بیان و کاربردی‌تر می‌باشد این است که اگر داده‌ی شما دارای طول متغییر است از VARCHAR و اگر این داده دارای طول ثابت است از CHAR استفاده کنید.

TINYTEXT

 مقادیر بین ۱ تا ۲۵۵ کاراکتر ثابت را در خود جای می‌دهد. تفاوت TINYTEXT و CHAR یا VARCHAR در این است که این نوع‌داده مقداری را به عنوان ورودی نمی‌پذیرد و میزان فضایی که اشغال ‌می‌کند برابر ۲۵۵ بایت است.

TEXT

مقادیر بین ۲ تا ۶۵,۳۵۳ کاراکتر ثابت معادل ۶۵ کیلوبایت را درون خود ذخیره می‌کند.

MEDIUMTEXT

مقادیر بین ۳ تا ۱۶,۷۷۷,۲۱۵ کاراکتر معادل ۱۶ مگابایت را درون خود ذخیره می‌کند.

LONGTEXT

مقادیر بین ۴ تا ۴,۲۹۴,۹۶۷,۲۹۵ کاراکتر معادل ۴ گیگابایت را درون خود ذخیره ‌میکند.

TINYBLOB

مانند TINYTEXT است اما به صورت باینری یعنی دقیقا همان فضا را اشغال می‌کند ولی به جای ذخیره کردن رشته‌ها، مقادیری متغییر عددی را که به صورت باینری (دو دویی) هستند ذخیره می‌کند. از این نوع داده معمولا برای ذخیره فایل تصاویر (نه مسیر تصویر) استفاده می‌شود.

BLOB

دقیقا مشابه TEXT‌ است اما با این تفاوت که مقداری که ذخیره می‌کند از نوع باینری و دو دویی است. فضایی که اشغال می‌کند و اعدادی که می‌گیرد دقیقا مشابه TEXT است.

MEDIUMBLOB

دقیقا این دستور نیز مشابه MEDIUMTEXT‌ است با تفاوت باینری بودن اطلاعاتی که ذخیره می‌شود. فضایی که اشغال می‌کند نیز دقیقا مشابه MEDIUMTEXT می‌باشد.

LONGBLOB

این نوع‌داده نیز عینا مشابه LONGTEXT‌ است با این تفاوت که اطلاعات را به صورت باینری ذخیره می‌کند. فضایی که اشغال می‌کند نیز مشابه LONGTEXT است.

ENUM('value1', 'value2', ...)

نوع‌داده‌ای است که آرایه‌ای از رشته‌ها بر اساس شماره‌بندی مخصوص به خودش که معمولا از ۰ شروع می‌شود درون خود ذخیره می‌کند. فضایی که اشغال می‌کند برابر ۱ یا ۲ بایت تا ۶۵۳۵۳ معادل ۶۵ کیلوبایت است.

SET('value1', 'value2', ...)

نوع داده‌ای است که آرایه‌ای از رشته‌ها را بر اساس شماره‌بندی خاصی که از ۰ شروع می‌شود درون خود ذخیره می‌کند، فضایی که اشغال می‌کند از ۱ تا ۶۴ عضو است.

تفاوت ENUM و SET

اگر نوع داده‌ای ENUM تعریف شود تنها و تنها یک مقدار از آرایه‌ها قابل دسترسی است و حق انتخاب چندین مقدار وجود ندارد. ولی حجم این رشته‌ها می‌توان تا ۶۵۳۵۳ بایت باشد. در حالیکه اگر نوع داده‌ای SET‌ تعیین شود، می‌توان به صورت همزمان چندین مقدار را از آن استخراج کرد ولی نهایت عضوی که درون آن قرار می‌گیرد، ۶۴ عضو است.

مثلا از ENUM‌ برای دکمه‌های رادیویی یا Radio Button و از SET برای لیست‌های انتخابی یا Select List‌ها استفاده می‌شود.

داده‌های عددی

TINYINT(size)

این نوع داده تنها داده‌های عددی مجاز در محدوده‌ی ۱۲۸- تا ۱۲۷ را شامل می‌شود و برای اعداد بدون علامت نیز محدوده‌ی ۰ تا ۲۵۵ را درون خود ذخیره می‌کند.  میزان فضایی که اشغال می‌کند برابر ۱ بایت است. مقادیر TINYINT‌ در واقع همان عبارتهای Boolean هستند که به صورت TRUE یا False نمایش داده خواهند شد.

SMALLINT(size)

این نوع داده تنها داده‌های عددی مجاز در محدوده‌ی ۳۲,۷۶۸- تا ۳۲,۷۶۸+ را شامل می‌شود و برای اعداد بدون علامت نیز محدوده‌ی ۰ تا ۶۵۵۳۵+ را درون خود ذخیره می‌کند. میزان فضایی که اشغال می‌کند برابر ۲ بایت است.

MEDIUMINT(size)

این نوع داده تنها داده‌های عددی مجاز در محدوده‌ی ۸,۳۸۸,۶۰۸- تا ۸,۳۸۸,۶۰۸+ را شامل می‌شود و برای اعداد بدون علامت نیز محدوده‌ی ۰ تا ۱۶,۷۷۷,۲۱۵+ را درون خود ذخیره می‌کند. میزان فضایی که اشغال می‌کند برابر ۳ بایت است.

INT(size)

این نوع داده برای ذخیره‌ی مقادیر عددی بین ۲,۱۴۷,۴۸۳,۶۴۸- تا ۲,۱۴۷,۴۸۳,۶۴۸+ را در خود ذخیره می‌کند. و در صورتیکه بخواهیم اعداد بی علامت (تنها مثبت) را درون آن ذخیره کنیم مقادیر ۰ تا ۴,۲۹۴,۹۶۷,۲۹۵ را می‌پذیرد. میزان فضایی که اشغال می‌کند برابر ۴ بایت است.

BIGINT(size)

این نوع داده برای ذخیره‌ی مقادیر عددی بین ۹,۲۲۳,۳۷۲,۰۳۶,۸۵۴,۷۷۵,۸۰۸- تا ۹,۲۲۳,۳۷۲,۰۳۶,۸۵۴,۷۷۵,۸۰۸+ را در خود ذخیره می‌کند. و در صورتیکه بخواهیم اعداد بی علامت (تنها مثبت) را درون آن ذخیره کنیم مقادیر ۰ تا ۱۸۴۴۶۷۴۴۰۷۳۷۰۹۵۵۱۶۱۵+ را می‌پذیرد. میزان فضایی که اشغال می‌کند برابر ۸ بایت است.

توجه: درنظر داریم که عبارت size همواره طول این اعداد را مشخص می‌کند و نمایانگر مقدار اعداد نیست. مثلا عبارت INT(5) در واقع تمام اعدادی که تا ۵ رقم هستند را در خود می‌پذیرد و درصورتیکه عددی با ۶ رقم وارد داده شود تنها ۵ رقم آن را می‌پذیرد. میزان فضایی که اشغال می‌کند برابر ۴ بایت است.

FLOAT(size, d)

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

FLOAT(5, 2)
Output: 999.99

حالا اگر در مثال فوق مقدار 999.009 به دیتابیس ارسال شود مقداری مشابه 999.01 ذخیره خواهد شد. یعنی یک تقریب حدودی میزنیم.

در صورتیکه پارامتر size از ۰ تا ۲۴ باشد (طول داده‌ها از ۰ تا ۲۴ باشد) فضایی که این داده اشغال می‌کند برابر ۴ بایت است. و اگر این مقدار از ۲۵ تا ۵۳ باشد فضایی که اشغال خواهد کرد ۸ بایت خواهد بود.

DOUBLE(size, d)

دقیقا مشابه FLOAT است با این تفاوت که نوع داده‌ی DOUBLE دقیق‌تر بوده و فضایی که اشغال می‌کند دو برابر فضای داده‌ی FLOAT‌ است. یعنی ۸ بایت. تعریف پارامترهای size و d نیز دقیقا مشابه FLOAT است.

DECIMAL(size,d)

دقیق‌ترین نوع داده‌ی اعشاری می‌باشد که به داده‌ی اعشاری با ممیز ثابت (محاسبه دقیق) معروف است. فضایی که این نوع داده اشغال می‌کند برابر ۱۲ بایت است. تعریف پارامترهای size و d دقیقا مشابه FLOAT می‌باشد.

تفاوت بین FLOAT و DOUBLE و DECIMAL:

• از داده‌های نوع FLOAT و DOUBLE‌ زمانی استفاده می‌شود که داده قابل اندازه‌گیری باشد (دقت مهم نیست). به عنوان مثال مسافت و فاصله
• از داده‌ی نوع DECIMAL‌ زمانی استفاده می‌شود که داده شمارشی باشد (دقت مهم است). به عنوان مثال: حقوق، پول و امتیاز

برای ذخیره کرده اعداد بدون علامت مثبت در کنار اعداد صحیح یا اعشاری دستور UNSIGNED استفاده می‌شود.

برای اضافه کردن صفر به قبل از یک داده‌ی عددی از عبارت ZEROFILL استفاده می‌شود. مثلا:

INT(5) -> ZEROFILL
Output: // 00005

داده‌های مربوط به تاریخ

DATE()

این داده برای ذخیره‌ی تاریخ بدون درنظر گرفتن ساعت استفاده می‌شود. مقادیری که می‌پذیرد از ۰۱-۰۱-۱۰۰۰ تا ۳۱-۱۲-۹۹۹۹ است. فضایی که این نوع داده اشغال می‌کند ۳ بایت است.

DATETIME()

از این نوع داده که پرکابردترین است برای ذخیره تاریخ به همراه ساعت استفاده می‌شود. محدوده‌ی تاریخی آن بین ۰۰:۰۰:۰۰ ۰۱-۰۱-۱۰۰۰۰ تا ۲۳:۵۹:۵۹ ۳۱-۱۲-۹۹۹۹ است. فضای اشغالی این داده ۸ بایت است.

TIMESTAMP()

نوعی داده‌ی ثبت تاریخ و ساعت است که هر دو مقدار را در یک فیلد یکتا قرار می‌دهد. مقادیری که میپذیرد از محدوده‌ی ۰۰:۰۰:۰۱ ۰۱-۰۱-۱۹۷۰ UTC یا گرینوینچ تا ۰۳:۱۴:۰۷ ۱۹-۰۱-۲۰۳۸ UTC گرینویچ می‌باشد. MySQL تاریخ Time Zone‌ (محلی) را دریافت کرده و به صورت UTC تبدیل می‌کند و در دیتابیس ذخیره می‌کند و هرگاه بخواهد آن را به کاربر نمایش دهد مجددا تاریخ UTC را به Time Zone‌ تبدیل کرده و به کاربر نمایش می‌دهد. فضایی که این نوع داده اشغال می‌کند برابر است با ۴ بایت.

TIME()

از این نوع داده برای نگه‌داری ساعت بدون تاریخ استفاده می‌شود که قالبهای آن به صورت HH:MM:SS یا HHH:MM:SS است. محدوده‌ی ساعت برای قالب دوم برابر ۸۳۸:۵۹:۵۹- تا ۸۳۸:۵۹:۵۹+ است و برای قالب اول برابر ۰۰:۰۰:۰۰ تا ۲۳:۵۹:۵۹. فضای اشغالی این نوع داده ۳ بایت است.

YEAR()

این نوع داده برای ذخیره سال بکار می‌رود و معمولا به صورت دو رقمی از بازه‌ی ۱۹۰۱ تا ۲۱۵۵ و برای داده‌های دو رقمی به از بازه‌ی ۷۰ تا ۶۹ است که معادل ۱۹۷۰ تا ۲۰۶۹ می‌باشد. فضایی که این نوع داده اشغال می‌کند برابر ۱ بایت است.

نوع داده‌ی JSON

JSON()

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

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