| ☰ 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:
String types
Numeric types
Date and time types
String types
The strings are enclosed by single quotation marks. Three String type are:
Fixed-Length Strings
Varying-Length Strings
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.
DATE
TIME
DATETIME
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 string. Variable length. Maximum length n |
BINARY(n) | Binary string. Fixed-length n |
BOOLEAN | Stores TRUE or FALSE values |
VARBINARY(n) or | 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
