×
☰ See All Chapters

MySQL Data Types

Data type is used to specify the type of data that variable can hold. A variable’s data type determines what operations can be carried out on the variable’s data, as well as the amount of memory needed to hold the data.

In MySQL we have 3 types of data types:

  1. String types 

  2. Numeric types 

  3. Date and time types 

String types

The strings are enclosed by single quotation marks. Three String type are:

  1. Fixed-Length Strings 

  2. Varying-Length Strings 

  3. Large Object Types 

Fixed-Length Strings

  • Strings that always have the same length are stored using a fixed-length data type. 

  • Can store alphanumeric data in this data type. 

  • An example of a constant length data type would be for a state abbreviation because all state abbreviations are two characters. 

CHAR(n)

CHARACTER(n)

n represents a number identifying the allocated or maximum length of the particular field with this definition.

Spaces are normally used to fill extra spots when using a fixed-length data type; if a field’s length was set to 10 and data entered filled only 5 places, the remaining 5 spaces would be recorded as spaces. The padding of spaces ensures that each value in a field is fixed length.

 

Be careful:

Fixed-length data type should be used when data is always fixed length like country code. If fixed-length data type is used and if value length exceeds then it results into error. Also if fixed-length data type is used and values are always less than the max length then database space is wasted.

Varying-Length Strings

  • Strings whose length is not constant for all data are stored using a Varying-length data type. 

  • Can store alphanumeric data in this data type. 

  • Spaces are not used to fill extra spots when using a Varying -length data type; For instance, if the allocated length of a varying-length field is 10, and a string of 5 characters is entered, the total length of that particular value would be only 5. Spaces are not used to fill unused places in a column. 

CHARACTER VARYING(n)

VARCHAR(n)

VARBINARY(n)

n represents a number identifying the allocated or maximum length of the particular field with this definition.

VARBINARY is similar to VARCHAR and VARCHAR2 of oracle except that it contains a variable length of bytes. Normally, you would use a type such as this to store some kind of digital data such as possibly an image file.

Large Object Types

  • These are variable-length data types used to hold longer lengths of data than what is traditionally reserved for a VARCHAR field. 

  • The BLOB and TEXT data types are two examples for large object types. 

  • A Binary Large Object (BLOB) is a MySQL data type that can store binary data such as images, multimedia, and PDF files. Its data is treated as a large binary string (a byte string). 

  • TEXT values are treated as non-binary strings (character strings) can be treated as a large VARCHAR field. It is often used when an implementation needs to store large sets of character data in the database. An example of this would be storing HTML content of website/blog enabling dynamic update of blog contents.  

Numeric Types

Decimal

DECIMAL(p,s)

Binary

BIT(n)

BIT VARYING(n)

Integer (no decimal)

INTEGER

SMALLINT

BIGINT

Floating-Point Decimals FLOAT(p,s)

DOUBLE PRECISION(p,s)

REAL(s)

The following are the standards for SQL numeric values:

p (Precision)  represents the total number of digits, and s (scale) is the number of digits after the decimal point. p must be greater or equal to s.

A common numeric data type in SQL implementations is NUMERIC(p,s), which accommodates the direction for numeric values provided by ANSI. Numeric values can be stored as zero, positive, negative, fixed, and floating-point numbers.

The following is an example using NUMERIC:

NUMERIC(5)

This example restricts the maximum value entered in a particular field to 99999. Note that all the database implementations that we use for the examples support the NUMERIC type but implement it as a DECIMAL.

Date and Time Types

Date and time data types are used to store date and time values. We can store only date or only time or both date and time based on the below specific data types.

  1. DATE 

  2. TIME 

  3. DATETIME 

  4. TIMESTAMP 

The elements of a DATETIME data type consist of the following:

  • YEAR 

  • MONTH 

  • DAY 

  • HOUR 

  • MINUTE 

  • SECOND 

Default date format for MySQL: yyyy-mm-dd

Inserting Date values:

Date value will be a string in format yyyy-mm-dd, here no conversion functions required like to_date as in oracle.

E.g.: ‘1989-6-27’

User-Defined Types

A user-defined type is a custom data type defined by developers or database administrators. It is not possible to define a new data type but you can customize the existing data type to the data storage requirement. The CREATE TYPE statement is used to create a custom data type.

For example, you can create a type as follows in both MySQL and Oracle:

CREATE TYPE EMPLOYEE_ID AS OBJECT

(NAME VARCHAR (30),

SERIAL_NUM VARCHAR (9));

You can reference your user-defined type as follows:

CREATE TABLE EMPLOYEE

(ID EMPLOYEE_ID,

SALARY DECIMAL(10,2),

DATE_OF_JOIN DATE DATE);

 

Notice that the data type referenced for the first column EMPLOYEE is PERSON. PERSON is the user-defined type you created in the first example.

Data type

Description

CHARACTER(n)

Character string. Fixed-length n

VARCHAR(n) or
CHARACTER VARYING(n)

Character string. Variable length. Maximum length n

BINARY(n)

Binary string. Fixed-length n

BOOLEAN

Stores TRUE or FALSE values

VARBINARY(n) or
BINARY VARYING(n)

Binary string. Variable length. Maximum length n

INTEGER(p)

Integer numerical (no decimal). Precision p

SMALLINT

Integer numerical (no decimal). Precision 5

INTEGER

Integer numerical (no decimal). Precision 10

BIGINT

Integer numerical (no decimal). Precision 19

DECIMAL(p,s)

Exact numerical, precision p, scale s. Example: decimal(5,2) is a number

 that has 3 digits before the decimal and 2 digits after the decimal

NUMERIC(p,s)

Exact numerical, precision p, scale s. (Same as DECIMAL)

FLOAT(p)

Approximate numerical, mantissa precision p. A floating number in base

10 exponential notations. The size argument for this type consists of a single

number specifying the minimum precision

REAL

Approximate numerical, mantissa precision 7

FLOAT

Approximate numerical, mantissa precision 16

DOUBLE PRECISION

Approximate numerical, mantissa precision 16

DATE

Stores year, month, and day values

TIME

Stores hour, minute, and second values

TIMESTAMP

Stores year, month, day, hour, minute, and second values

INTERVAL

Composed of a number of integer fields, representing a period of time,

 depending on the type of interval

ARRAY

A set-length and ordered collection of elements

MULTISET

A variable-length and unordered collection of elements

XML

Stores XML data

Below table gives you the difference between Oracle and MySQL data types:

Data Type

Oracle

MySQL

Integer

Number

Int

Integer

Float

Number

Float

String (Fixed)

Char

Char

String (Variable)

Varchar

Varchar1

Varchar

Binary object

Long

Raw

Blob

Text

Domains

A domain is a set of valid data types that can be used. A domain is associated with a data type, so only certain data is accepted. After you create a domain, you can add constraints to the domain. Constraints work in conjunction with data types, allowing you to further specify acceptable data for a field. The domain is used like the user-defined type.

You can create a domain as follows:

CREATE DOMAIN MONEY_D AS NUMBER(8,2);

You can add constraints to your domain as follows:

ALTER DOMAIN MONEY_D

ADD CONSTRAINT MONEY_CON1

CHECK (VALUE > 5);

You can reference the domain as follows:

CREATE TABLE EMP_PAY

(EMP_ID NUMBER(9),

EMP_NAME VARCHAR2(30),

PAY_RATE MONEY_D);

 


All Chapters
Author