Data Types

Character Data

char(20)
varchar(20)

The maximum length for char is 255 bytes, varchar columns is 65,535 bytes.

For Oracle Database, use varchar2 type when defining variable-length character columns.

Character Sets

MySQL can store data using various character sets, both single- and multibyte. To view the supported character sets in your server, you can use the show command.

SHOW CHARACTER SET;

To choose a character set when defining a column:

varchar(20) character set utf8

With MySQL, you may set default character set for your entire database:

create database db_name character set utf8;

Text Data

If you need to store data that might exceed the 64 KB limit for varchar columns, you will need to use one of the text types.

MySQL Text Types

Text Type	Maximum number of bytes
Tinytext	255
Text	65,535 (~64KB)
Mediumtext	16,777,215 (~16MB)
Longtext	4,294,967,295 (~4GB)

Notes

• If data exceeds maximum size, the data will be truncated.
• Trailing spaces will not be removed.
• When using text columns for sorting or grouping, only the first 1024 bytes are used (limit may be increased).
• SQL Server has a single text type for large character data. DB2 and Oracle use a data type called clob (Character Large Object)

Oracle Database:

• 2000 bytes for char
• 4000 bytes for varchar2

SQL Server:

• 8000 bytes for both char and varchar

Numeric Data

MySQL Integer Types

Type	Signed Range	Unsigned Range
Tinyint	−128 to 127	0 to 255
Smallint	−32,768 to 32,767	0 to 65,535
Mediumint	−8,388,608 to 8,388,607	0 to 16,777,215
Int	−2,147,483,648 to 2,147,483,647	0 to 4,294,967,295
Bigint	−9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073,709,551,615

MySQL Floating-Point Types

Type	Numeric Range
$Float(p,s)$	−3.402823466E+38 to −1.175494351E-38 and 1.175494351E-38 to 3.402823466E+38
$Double(p,s)$	−1.7976931348623157E+308 to −2.2250738585072014E-308 and 2.2250738585072014E-308 to 1.7976931348623157E+308

you can specify a precision (the total number of allowable digits both to the left and to the right of the decimal point) and a scale (the number of allowable digits to the right of the decimal point), but they are not required.

float(4,2) will store a total of four digits, two to the left of the decimal and two to the right of the decimal. Such a column would handle the numbers 27.44 and 8.19 just fine, but the number 17.8675 would be roun- ded to 17.87, and attempting to store the number 178.375 in your float(4,2) column would generate an error.

Temporal Data

MySQL Temporal Types

Type	Default Format	Allowable Values
Date	YYYY-MM-DD	1000-01-01 to 9999-12-31
Datetime	YYYY-MM-DD HH:MI:SS	1000-01-01 00:00:00 to 9999-12-31 23:59:59
Timestamp	YYYY-MM-DD HH:MI:SS	1970-01-01 00:00:00 to 2037-12-31 23:59:59
Year	YYYY	1901 to 2155
Time	HHH:MI:SS	−838:59:59 to 838:59:59

If you wanted to insert the date March 23, 2005 into a date column using the default format YYYY-MM-DD, you would use the string ‘2005-03-23’

Date Format Components

Component	Definition	Range
YYYY	Year, including century	1000 to 9999
MM	Month	01 (January) to 12 (December)
DD	Day	01 to 31
HH	Hour	00 to 23
HHH	Hours (elapsed)	−838 to 838
MI	Minute	00 to 59
SS	Second	00 to 59

Date Conversions

The following would fail, since ‘DEC-21-1980’ violates default date format.

UPDATE person
SET birth_date = 'DEC-21-1980'
WHERE person_id = 1;

To convert string to date:

UPDATE person
SET birth_date = str_to_date('DEC-21-1980', '%b-%d-%Y')
WHERE person_id = 1;

Placeholder	Description
%a	The short weekday name, such as Sun, Mon, …
%b	The short month name, such as Jan, Feb, …
%c	The numeric month (0..12)
%d	The numeric day of the month (00..31)
%f	The number of microseconds (000000..999999)
%H	The hour of the day, in 24-hour format (00..23)
%h	The hour of the day, in 12-hour format (01..12)
%i	The minutes within the hour (00..59)
%j	The day of year (001..366)
%M	The full month name (January..December)
%m	The numeric month
%p	AM or PM
%s	The number of seconds (00..59)
%W	The full weekday name (Sunday..Saturday)
%w	The numeric day of the week (0=Sunday..6=Saturday)
%Y	The four-digit year

NULL

Scenarios

• Not Applicable: column not used
• Unknown Value: optional column
• Value Undefined

Notes

• An expression can be null, but it can never equal null
• Two nulls are never equal to each other

SELECT emp_id, fname, lname, superior_emp_id
FROM employee
WHERE superior_emp_id IS NULL
	AND emp_id IS NOT NULL;