MySQL Data Types

July 4, 2013

When we see about PHP data types, we have listed set of possible types of data with which the PHP variable may be initialized. Similarly, MySQL supports huge list of data types, and it is mandatory to specify the data type of each attribute of a table, unlike PHP, a loosely typed language that we have seen with PHP Data Type Conversion.

MySQL supported data types that are huge in number, falls into the three main categories. These are,

  1. Numeric Data Type
  2. String Data Type
  3. Date and Time Data Type

Numeric Data Type

On creating MySQL table with attributes with numeric data type, then this attribute will accept all data contains only signed and unsigned numbers. These number values can be any one of the types, like, integers, floating point numbers, decimals, booleans and etc. Again the list of MySQL supported numeric data types falls into the following categories.

  • Integers – Based on the number of bytes allotted for storage, these numeric typed data are classified as TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT.
  • Floating point values
    • This data type category also contains two types as FLOAT and DOUBLE, for representing single and double precision floating point data, respectively.
    • These type of data is used to store the values by specifying its precision, which will truncate the numbers leading after decimal point, and produce approximate value. For example, if we specify (1,2) to the floating point value 3.88333, then the approximate value will be 3.88.
  • Decimal / Numeric – This data holds exact values without approximation, and so called as fixed point data values.
  • Binary values  – Obviously, it contains values with 0’s and 1’s.

mysql_data_type

String Data Type

MySQL provide several string data type, like, CHAR, VARCHAR, TEXT and etc., as shown below.

  • CHAR / VARCHAR
    • Both are deferred with respect the length property of the values it holds. CHAR holds fixed length string, where as VARCHAR holds variable length strings instead.
    • And, these are also differed with respect to warning messages generated on removing excessive trailing spaces.
  • BLOB / TEXT-
    • MySQL BLOB typed attribute can accept binary data about large objects, files and etc, where as, TEXT data type can hold large text data.
    • With respect to the max length, BLOB and TEXT data types are further classified as, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB and similarly, TINYTEXT , TEXT , MEDIUMTEXT, LONGTEXT.
  • BINARY / VARBINARY – Both are differed with the fixed and variable length order of binary strings instead of character string and has no character set like BLOB.
  • ENUM / SET  – ENUM typed attribute will accept values which is limited with the given set of values. And, SET can have entire set of values as a whole.

Date and Time Data Type

These kind of MySQL data types are provided for representing temporal data that are used for MySQL date / time manipulation. Common Date and Time related types supported by MySQL are as follows.

  • DATE – With this data type MySQL table attribute will accept date value in YYYY-MM-DD format.
  • TIME – This will accept the time part of an temporal data in HH:MM:SS format.
  • DATETIME – Obviously, will allow data that holds both date and time by combining the above two, as YYYY-MM-DD HH:MM:SS.
  • YEAR – Using this data type, we can store year of a date, either in 2 digits that has last two digits of a year or 4 to have entire year value as it is.
  • TIMESTAMP – This also includes both date and time value as like as DATETIME. And, MySQL performs timestamp conversion  process between current timezone and UTC, while storing / retrieving.

Example: MySQL Data Type:

Now, we are going to write a MySQL query for creating table by specifying table attributes and their corresponding data types.

CREATE TABLE event(name VARCHAR(255), date DATETIME, status ENUM(‘ACTIVE’,’INACTIVE’));

The above query will create a new table named as event containing event name, event date and its status with the data types as specified. And, the table structure, after executing this query is as shown below.

mysql_event_table

Now, let us insert row of data into the event table by executing the following query.

INSERT INTO event (name, date, status) VALUES ('Birthday Party', '2013-07-06 18:30:00', 'ACTIVE');

Since, the values set with the INSERT query is with right data type as specified on creating event table, the above query will successfully be executed and a new row will be added as a result.

If we set query data with improper data type not matched with actual attribute data type, for example, if we set the value event status which neither ACTIVE nor INACTIVE, let us examine what would happen. Now the query is,

INSERT INTO event (name, date, status) VALUES ('Birthday Party', '2013-07-06 18:30:00', 'TRUE');

While executing the above query for trying to store value TRUE for event status, it will fail, rather store NULL value instead.

Add a Comment