MySQL Data Types

When we see PHP data types, we have listed a set of possible types of data with which the PHP variable may be initialized. Similarly, MySQL supports a 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, fall 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 a MySQL table with attributes with a numeric data type, then this attribute will accept all data containing 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, and BIGINT.
• Floating-point values
  • This data type category also contains two types FLOAT and DOUBLE, for representing single and double-precision floating-point data, respectively.
  • This type of data is used to store the values by specifying its precision, which will truncate the numbers leading after the 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 is so-called as fixed-point data values.
• Binary values  – Obviously, it contains values with 0’s and 1’s.

String Data Type

MySQL provides more string data-type, like CHAR, VARCHAR, TEXT and etc., as shown below.

• CHAR / VARCHAR
  • Both are deferred with respect to the length property of the values it holds. CHAR holds the fixed-length strings, whereas VARCHAR holds variable-length strings instead.
  • And, these also differ with respect to warning messages generated by removing excessive trailing spaces.
• BLOB / TEXT–
  • MySQL BLOB typed attribute can accept binary data about large objects, files and etc, whereas, the 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 the character string and have no character set like a BLOB.
• ENUM / SET  – ENUM typed attribute will accept values that are limited by the given set of values. And, SET can have an entire set of values as a whole.

Date and Time Data Type

This kind of MySQL data type is 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 the date value in YYYY-MM-DD format.
• TIME – This will accept the time part of 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 a year of a date, either in 2 digits that have the last two digits of a year or 4 to have the entire year value as it is.
• TIMESTAMP – This also includes both date and time values like as DATETIME. And, MySQL performs timestamp conversion process between current time zone and UTC, while storing/retrieving.

Example: MySQL Data Type:

Now, we are going to write a MySQL query for creating a 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 an event containing the event name, event date and its status with the data types as specified. And, the table structure, after executing this query is shown below.

Now, let us insert a 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 for the INSERT query are with the right data type as specified in creating the 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 an 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.

Important: If you are going to load the user-entered data into the query, then using a prepared statement is mandatory.

This example handles the user-submitted data with a prepared statement. It will show the behavior of MySQL insert with respect to the data type configured.

The select option of the form has the valid ENUM options and also an invalid option. If the user submits invalid data, then the INSERT operations will not be performed.

<?php
if (isset($_POST['insert-event'])) {
    $conn = mysqli_connect("localhost", "root", "", "sql_data_type");
    $sql = $conn->prepare("INSERT INTO event  (name, date, status) VALUES (?, ?, ?)");
    $name = $_POST["event_name"];
    $date = $_POST["event_date"];
    $status = $_POST["event_status"];
    $sql->bind_param("sss", $name, $date, $status);
    $result = $sql->execute();
}
?>

<form method="post">
	<input type="text" name="event_name" placeholder="Name" value="Vincy" />
	<input type="date" name="event_date"
		value="<?php echo date('Y-m-d'); ?>" /> <select name="event_status">
		<option value="ACTIVE">ACTIVE</option>
		<option value="INACTIVE">INACTIVE</option>
		<option value="Invalid">Invalid</option>
	</select> 
	<input type="submit" name="insert-event" value="Insert Event" />
</form>

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
• jQuery
• JavaScript
• WordPress
• MySQL
  • MySQL Basics
    • MySQL Data Types
    • MySQL Database Files
    • MySQL SHOW
    • MySQL CONCAT
    • MySQL OrderBy
    • MySQL Date Time
    • MySQL JOINs: An Introduction
    • MySQL Left Join
    • MySQL Right JOIN
    • MySQL vs MySQLi in PHP
  • phpMyAdmin
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js