Типы данных в MySQL (сжатый справочник для PHP программиста)

    Что и как и как много хранит MySQL?

    Данный материал создан специально для программистов, которые быстро смогут определиться какой тип данных лучше выбрать для хранения значений в БД MySQL.

    Для затравки, интересная цитата из мануала по MySQL:
    «Максимальный размер записи в MyISAM составляет 65534 байтов. Каждый BLOB или TEXT-столбец засчитывается здесь как 5-9 байтов.» — как это трактовать однозначно не понятно. НО ясно что много-примного столбцов в таблицу на засунешь. «Извращенистые программисты» — будьте аккуратны (66000 столбцов вы точно создать не сможете).

    UPD: Если найдете ошибку, или я что-то где-то утаил — отпишитесь в комментах, с удовольствием добавлю.

    UPD1 В первую очередь (и самый лучший вариант для новичков) прочитать ОФИЦИАЛЬНЫЙ МАНУАЛ dev.mysql.com/doc/refman/5.0/en/data-types.html (спасибо Psyh за прямую ссылку), а здесь вырезка для META обработчиков данных (как в лице программистов так и в лице машинной обработки).

    UPD2 В принципе, все что написано ниже, можно прочитать по адресу www.mysql.ru/docs/man/Column_types.html (за ссылку «русского перевода», спасибо artuska).

    UPD3 Еще одну неплохую ссылку предоставил 4all: newcontinent.ru/h/mysqlc (материал на русском)

    UPD4 Цитата из комментов от egorF:
    # 14«Как главный редактор русскоязычного перевода доки на MySQL, я рекомендую в него не заглядывать — он уже сказочно морально устарел.»



    Следующий массив вполне понятен PHP программистам.
    Да и вообще, любые уважающие себя программисты это поймут.

    Например:
    'int'=>Array('byte'=>4, 'min'=>-2147483648, 'max'=>2147483647, 'umin'=>0, 'umax'=>4294967295),

    Трактуется следующим образом:
    Тип поля INT использует 4 байта для хранения значения. Минимально значение -2147483648, максимальное 2147483647. Беззнаковый INT (unsigned) хранит значения от 0 до 4294967295.

    Используется так:
    Мне надо сохранить в поле максимальное числовое значение 234 259 000 000 000.
    INT — для этого не подходит. Смотрим другие типы и находим, что BIGINT вполне подойдет.

    $MYSQL_TYPES=Array(
       // INTEGER
       // byte — кол-во байт на хранение,
       // max/min — предельные значения,
       // umax/umin — беззнаковые предельные значения
       'int'=>Array('byte'=>4, 'min'=>-2147483648, 'max'=>2147483647, 'umin'=>0, 'umax'=>4294967295),
       'bigint'=>Array('byte'=>8, 'min'=>-9223372036854775808, 'max'=>9223372036854775807, 'umin'=>0, 'umax'=>18446744073709551615),
       'tinyint'=>Array('byte'=>1, 'min'=>-128, 'max'=>127, 'umin'=>0, 'umax'=>255),
       'smallint'=>Array('byte'=>2, 'min'=>-32768, 'max'=>32767, 'umin'=>0, 'umax'=>65535),
       'mediumint'=>Array('byte'=>3, 'min'=>-8388608, 'max'=>8388607, 'umin'=>0, 'umax'=>16777215),

       // DECIMAL   DECIMAL(M,D) m — кол-во цифр (max 65 цифр), d — сколько из них могут быть после запятой
       // min_byte/max_byte — краевые значения размера поля в байтах,
       // byte_formula — формула вычисления размерности
       // length — максимальное кол-во цифр
       'decimal'=>Array('min_byte'=>2, 'max_byte'=>67, 'byte_formula'=>'(D==0?(M+1):(M+2)', 'length'=>65),
       'dec'=>Array('min_byte'=>2, 'max_byte'=>67, 'byte_formula'=>'D==0?(M+1):(M+2)', 'length'=>65),
       'numeric'=>Array('min_byte'=>2, 'max_byte'=>67, 'byte_formula'=>'D==0?(M+1):(M+2)', 'length'=>65),
       
       // FLOAT DOUBLE 
       // Внимание! Не храните денежные значения в этих полях!!! Деньги надо хранить — в DECIMAL
       // у FLOAT ТОЧНОСТЬ ТОЛЬКО 7 ЦИФР!!! (все остальные цифры «смазываются»)
       // у DOUBLE ТОЧНОСТЬ ТОЛЬКО 15 ЦИФР!!! (все остальные цифры «смазываются»)
       // byte — кол-во байт для хранения поля (по-умолчанию)
       // max_byte — максимальное кол-во байт для хранения
       // negative_min/negative_max — минмаксы для отрицательных чисел
       // positive_min/positive_max — минмаксы для положительных чисел
       'float'=>Array('byte'=>4, 'max_byte'=>8, 'negative_min'=>-3.402823466E+38, 'negative_max'=>-1.175494351E-38, 'positive_min'=>1.175494351E-38, 'positive_max'=>3.402823466E+38),
       'double'=>Array('byte'=>8, 'negative_min'=>-1.7976931348623157E+308, 'negative_max'=>-2.2250738585072014E-308, 'positive_min'=>2.2250738585072014E-308, 'positive_max'=>1.7976931348623157E+308),

       // BOOLEAN
       // сами все поймете
       'bool'=>Array('byte'=>1, 'true'=>1, 'false'=>0),
       'boolean'=>Array('byte'=>1, 'true'=>1, 'false'=>0),
       
       // VARCHAR
       // byte — кол-во байт отведенных для хранения (можно задать меньше)
       // min_byte — минимальное кол-во байт в которых может храниться поле (если длина равна 1)
    // В MYSQL 5.0.3 и выше, VARCHAR может быть до 65,535 символов!!!
       // length — максимальная длина символов в поле
       'varchar'=>Array('byte'=>256, 'min_byte'=>2, 'length'=>255),
       'char'=>Array('byte'=>256, 'min_byte'=>2, 'length'=>255),
          
       // TEXT
       // byte — кол-во байт для хранения поля
       // min_byte — минимальное кол-во байт для хранения одного символа (если длина поля равна 1)
       // length — максимальное количество символов в поле
       'tinytext'=>Array('byte'=>256, 'min_byte'=>2, 'length'=>255),
       'text'=>Array('byte'=>65537, 'min_byte'=>3, 'length'=>65535),
       'mediumtext'=>Array('byte'=>16777218, 'min_byte'=>4, 'length'=>16777215),
       'longtext'=>Array('byte'=>4294967300, 'min_byte'=>5, 'length'=>4294967296),
       'tinyblob'=>Array('byte'=>256, 'min_byte'=>2, 'length'=>255),
       'blob'=>Array('byte'=>65537, 'min_byte'=>3, 'length'=>65535),
       'mediumblob'=>Array('byte'=>16777219, 'min_byte'=>4, 'length'=>16777215),
       'longblob'=>Array('byte'=>4294967300, 'min_byte'=>5, 'length'=>4294967296),
       
       // DATETIME
       // byte — кол-во байт для хранения значения поля
       // mask — стандартная маска ввода значения (есть куча других вариантов, о них читайте в мануале)
       // min/max — минимальные максимальные значения дат которые сохраняют поля
       'datetime'=>Array('byte'=>8, 'mask'=>'YYYY-MM-DD HH:MM:SS', 'min'=>'1000-01-01 00:00:00', 'max'=>'9999-12-31 23:59:59'),
       'date'=>Array('byte'=>3, 'mask'=>'YYYY-MM-DD', 'min'=>'1000-01-01', 'max'=>'9999-12-31'),
       'time'=>Array('byte'=>3, 'min'=>'-838:59:59', 'max'=>'838:59:59'),
       'year'=>Array('byte'=>1, 'min'=>1901, 'max'=>2155),
       'timestamp'=>Array('byte'=>4, 'mask'=>Array(14=>'YYYYMMDDHHMMSS',12=>'YYMMDDHHMMSS',10=>'YYMMDDHHMM',8=>'YYYYMMDD',6=>'YYMMDD',4=>'YYMM',2=>'YY'), 'min'=>1970, 'max'=>2036 ),
       
       // ENUM
       // byte — кол-во байт на хранение поля
       // max_byte — максимальное кол-во байт, которое можно достигнуть при максимальном кол-ве элементов
       // max_number_of_element — кол-во элементов, которое может содержать поле
       'enum'=>Array('byte'=>1, 'max_byte'=>2, 'max_number_of_element'=>65535),
       'set'=>Array('byte'=>1, 'max_byte'=>8, 'max_number_of_element'=>64)
    );
    * This source code was highlighted with Source Code Highlighter.


    Поделиться публикацией
    Похожие публикации
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 38
    • +2
      Слегка сумбурно и не очень понятно. ИМХО, dev.mysql.com/doc/refman/5.0/en/data-types.html всё же лучше, к тому же описывает все особенности работы с типами.
      • 0
        Полностью с тобой согласен. Сперва мануал! (Увы, многие его толком не читают, потом приходится искать иголку в стоге сена: «Ввели одно, получили другое.»)

        У меня была задача сделать программную предпроверку всех сохраняемых данных в базу. Чтобы не получить СЮРПРИЗ при считывании из нее. Поэтому выложил промежуточный вариант описания данных в виде такого массива.
      • +1
        Такие тонкие вопросы при реальной работе лучше всегда перепроверять в официальной документации: dev.mysql.com/doc/refman/5.0/en/data-types.html

        (Это не упрёк автору, это хорошая практика разработки. Хотя с другой стороны, новичок в базах данных, который видит это разнообразие типов впервые, обязан всё равно читать документацию. В общем, не совсем ясно, может ли такая простыня из цифр быть кому-нибудь полезна...)
        • 0
          Считаю, что может быть полезной для машинной обработки сохраняемых данных.
        • +1
          Думаю стоит ещё описать разницу типов, которые у вас тут описаны как одинаковые по сути, например decimal, blob/text и тп.
          • 0
            К сожалению, эту тему в данный момент нет времени раскрыть. Но как подвернется свободная минута — обязательно.
          • 0
            Интересно узнать, кто как хранит дату и время? Поделитесь опытом.
            Я как-то увидел, что в CMS MODx дата/время храниться в поле типа INT. Мне понравилось и теперь делаю также ))
            • 0
              Очень удобно хранить время в UNIX_TIMESTAMP, быстро, шустро. С диапазонами при хорошей математике вообще нет никаких проблем — делай как хочу и что хочу.

              Правда неудобно смотреть на даты типа 1218635068 (кто слёту скажет, что это равно 2008-08-13 13:44:28 [я не ошибся?])

              Правда к счастью, МУСКЛ хорошо и быстро справляется, что с полями ДатыВремени что с Числовыми.

              Вобщем как вам удобно.
              • НЛО прилетело и опубликовало эту надпись здесь
                • +1
                  Ну если до 2038 у Вашего приложения дожить перспектив нет, то можно и unix timestamp'ами даты хранить. Вообще же единственные достоинтсва такого способа — экономия места и простота ввода-вывода из базы/в базу (не надо думать про локали, timezone'ы и прочее). Из недостатков — узкий диапазон дат, секундное разрешение (да, у DATETIME в MySQL оно тоже секундное; в других DBMS это может быть и по-другому, как, например, как в PostgreSQL), неудобство работы с датами (типа: составить отчет на понедельник второй недели марта).
                  • 0
                    всегда DATETIME
                    только его.
                    Иначе многообразие функций для работы с датами которое есть в мускуле идет коту под хвост.
                    Меньше кода в пхп — все перенесено на уже имеющиеся более быстрые функции в бд. и ничего не надо захламлять в пхп. ну и как уже написали про 38ой год… не знаю что будет через 30 лет но хочется надеяться что мои приложения доживут до этого или хотябы структура базы доживет :)
                  • 0
                    Вот не понятно с текстовыми полями. Каким образом там взялись 65537, для записи этого числа, требуется уже не 16 байт, а 17. Что уже не кратно 8, значит не удобно для хранения в памяти.
                    Ничего не перепутано?
                    • 0
                      Насколько я понял со скупой документации, для поля TEXT длиной 65535, используется 65535 байт на хранение самого текста + 2 байта служебных.

                      TINETEXT — 1 байт служебный,
                      в MEDIUMTEXT уже 3 байта.

                      Для чего и как, затрудняюсь ответить…
                      • +1
                        TINETEXT может быть длиной до 255 байт; для хранения самой длины достаточно одного беззнакового байта (0-255); суммарная длина 255 + 1 = 256.

                        MEDIUMTEXT может быть длиной до 16777215 = 2^24-1; для хранения длины достаточно трёх байт без знака; суммарная длина 16777215 + 3 = 16777218. А у вас 16777219. Откуда взяли цифру?

                        Кстати, я же говорил что нужно читать документацию, верно? Тут ведь всё написано dev.mysql.com/doc/refman/5.0/en/storage-requirements.html (первый абзац после таблицы Storage Requirements for String Types)
                        • +1
                          «цифру 9?» *
                          • 0
                            Да очень просто у меня получилось: 5 + 3 = 9 (Как будто и в моем калькуляторе дырочку просверлить нельзя :) ).

                            Спасибо, ошибку исправил. И запомнил что 5 + 3 = 8 )))
                    • 0
                      а можно вопрос.
                      буквально вчера читал документацию по типу decimal:
                      DECIMAL[(M[, D])] пишется что M — количество цифр до запятой, D — количество цифр после запятой.

                      получается что тип DECIMAL(6,6) должен позволить мне записать в ячейку число типа 123456.123456
                      но на практике число 123456.123456 превращается в 0.999999

                      почему такое происходит? скорее всего я что-то делаю не так.

                      • 0
                        В тексте могли взглядом не заметить.

                        Цитата из текста:
                        «DECIMAL(M, D) m — кол-во цифр (max 65 цифр), d — сколько из них могут быть после запятой».

                        Т.е. M это общее число цифр. А вот сколько из них забрать под десятичную часть.

                        Т.е. DECIMAL(6,6) из 6ти доступных цифр, все их определил как десятичные.
                      • 0
                        вопрос снят. виной всему моя невнимательность

                        DECIMAL[(M[, D])] M — общее количество цифр, D — количество цифр после запятой.
                        то биш что бы сохранить число 123456.123456 мне нужен тип DECIMAL(12,6)
                      • +2
                        Не могу понять, как этот топик в хабраленту попал
                        • –3
                          Давим на большинство.

                          Боюсь ошибиться, но по-моему здесь каждый второй знает и работает на PHP и MySQL.
                          • +1
                            Даже если так, то и этот народ ничего нового не почерпнет из статью: эта информация в более удобоваримой форме доступна на mysql.com
                        • НЛО прилетело и опубликовало эту надпись здесь
                          • +2
                            Я вот не понял.
                            Вы этот массив в скриптах собираетесь использовать? Тогда каким образом? И главное зачем?
                            А если нет, то зачем в таком виде? Табличкой понятнее было бы.
                            • 0
                              Это веб 3.0.
                              • 0
                                Неожиданно, я от веб3.0 ждал большего, гораздо большего…
                              • 0
                                Я че-то тоже не врубился… Автор, кстати, пробовал вообще в PHP писать 'umax'=>4294967295, не говоря уже про 'max'=>9223372036854775807?
                                • 0
                                  Эти числа идут через обертку классов (В коде они просто убраны).

                                  Для себя, если надо использовать (в хелпере например), возмите эти числа в кавычках и используйте. )
                              • +1
                                К вашему сведению, в пятой версии MySQL varchar может быть и больше 255 символов.
                                • 0
                                  Да есть такая информация, но насколько я помню это начинается с какойто версии 5.0.XXX,
                                  т.к. в 5.0.1 этого еще не было.
                                  • 0
                                    *какой-то

                                    Посмотрел в мануале с версии 5.0.3 начинается.
                                • +2
                                  Что я хотел бы увидеть под заголовком «Типы данных в MySQL (сжатый справочник для PHP программиста)»?

                                  В MySQL есть довольно много разнообразных типов данных, причём многие из них ещё могут быть записаны под разными именами (из соображений совместимости) и иметь множество несущественных опций. На практике вам почти никогда не потребуется тщательно раздумывать над особенностями реализации, например, различных строковых типов; поэтому дальше — краткий перечень самых базовых сведений, которые вам действительно понадобятся.

                                  Целые числа — integer.
                                  Вещественные числа — double.
                                  Булевы значения — bool (принимают значения true и false; или 1 и 0).
                                  Перечисления — enum('male', 'female').
                                  Строковые значения — varchar(L), если L (максимальная длина) меньше 65536, или text(L) в ином случае.
                                  Бинарные данные — blob(L), где L — максимальный размер.
                                  Дата и время — datetime.

                                  Остальное обычно не требуется, однако при необходимости вы всегда можете прочесть dev.mysql.com/doc/refman/5.0/en/data-types.html
                                  • +1
                                    дополню ссылочкой в тему:
                                    очень хороший учебник
                                    newcontinent.ru/h/mysqlc/
                                    • +1
                                      Автору большое спасибо. С массивом идея хорошая — доходчиво и понятно ))

                                      Только я бы структурировал по возрастанию информацию о подтипах, в TEXT и INT особенно, так тконечно разобраться можно, но при расположении по возрастанию еще и зрительно бы запоминалось лучше.

                                      И насчет VARCHAR уточнение, из мануала собственно: «The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.» — начиная с версии 5.0.3 уже не 0-255, а 0- 65,535.
                                      • +2
                                        Как главный редактор русскоязычного перевода доки на MySQL, я рекомендую в него не заглядывать — он уже сказочно морально устарел.
                                        • 0
                                          Строка для примера: 'char'=>Array('byte'=>256, 'min_byte'=>2, 'length'=>255)

                                          А вот и нет.
                                          Значение byte зависит от кодировки, т. е. для utf-8 будет не 255, а 255 * 3 = 765 байт. И +1 байт, который содержит количество символов (НЕ БАЙТОВ!).

                                          Ну а length (если это про количество символов) указано верно.

                                          зы: читал по диагонали, так что если что перепутал, извиняйте.
                                          • 0
                                            О! теперь бы справочник по postgreSQL :)

                                            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.