✖

All TalkersCode Topics

Follow TalkersCode On Social Media

✖

devloprr.com - A Social Media Network for developers Join Now ➔

MySQL Data Types

MySQL Data Types are very important to know beacuse they specify what kind of information you store in your columns and they can save a lot of space of your database.We should use only the type and size of field we really need to use


MySQL have many different types of data types like numeric, character, large strings or binary data, date and time and spatial.



Numeric Data Types

  • INT: it requires 4 bytes of storage.Signed values ranges from -2147483648 to 2147483647 and for Unsigned values ranges from 0 to 4294967295.

  • BIGINT: it requires 8 bytes of storage.Signed values ranges from -9223372036854775808 to 9223372036854775807 and for Unsigned values ranges from 0 to 18446744073709551615.

  • MEDIUMINT: it requires 3 bytes of storage.Signed values ranges from -8388608 to 8388607 and Unsigned values ranges from 0 to 16777215.

  • SMALLINT: it requires 2 bytes of storage.Signed values ranges from -32768 to 32767 and Unsigned values ranges from 0 to 65535.

  • TINYINT: it requires 1 bytes of storage.Signed values ranges from -128 to 127 and Unsigned values ranges from 0 to 255

  • DECIMAL(M,D): it bytes vary.Fiexd Precision Numbers where M is the maximum numbers of total digits and D is the number of decimals. M can be range from 1 to 65 and D ranges from 0 to 30.

  • DOUBLE(M,D) it requires 8 bytes. Double-precision floating-point numbers from -1.7976x10308 to 1.7976x10308.

  • FLOAT(M,D): it requires 4 bytes.Single-precision floating-point numbers from -3.4028x1038 to 3.4028x1038


Character Data Types

  • CHAR(M): fiexd length strings of character data where M is the number of characters, between 0 to 255.

  • VARCHAR(M): variable length of strings of character data M is the maximum number of characters, between 0 to 255.

  • ENUM: it requires 1 to 2 bytes store one value selected from a list of acceptable values.

  • SET: it requires 1 to 8 bytes store 0 or more value selected from a list of acceptable values.


Large String or Binary Data Types

  • TEXT: variable-length of strings of characters upto 65kb in length.

  • TINYTEXT: variable-length of strings of characters upto 255Bytes in length.

  • MEDIUMTEXT: variable-length of strings of characters upto 16MB in length.

  • LARGETEXT: variable-length of strings of characters upto 4GB in length.

  • BLOB: variable-length of strings of binary data upto 65kb in length.

  • TINYBLOB: variable-length of strings of binary data upto 255Bytes in length.

  • MEDIUMBLOB: variable-length of strings of binary data upto 16MB in length.

  • LARGEBLOB: variable-length of strings of binary data upto 4GB in length.



Date and Time Data Types

  • DATE: it requires 3 bytes dates from january 1, 1000 through december 31,9999. The default format for entry and display is "yyyy-mm-dd".

  • TIME: it requires 3 bytes. Time ranges from -838:59:59 through 838:59:59. The default format for entry and display is "hh:mm:ss" .

  • DATETIME: it requires 8 bytes. Combination of date and time from midnight january 1,1970 to december 31,9999. The default format for entry and display is "yyyy-mm-dd hh:mm:ss" .

  • TIMESTAMP: it requires 4 bytes. Combination of date and time from midnight january 1,1970 to year 2037. The default format for entry and display is "yyyy-mm-dd hh:mm:ss"

  • YEAR(M): it requires only 1 byte. Years in 2-digit or 4-digit format. The default is 4-digit format.In 4 digit format values from 1901 to 2155.In 2 digit values from (19)70 to (20)69.

❮ PrevNext ❯