MySQL Data Types: An In-Depth Guide

Series Introduction

For relational databases, table design and SQL writing are crucial, often accounting for over 90% of performance impact. This series explains these two knowledge areas in detail.

The language is easy to understand with many examples.

Target Audience

MySQL DBAs

MySQL developers

People migrating from other databases to MySQL

Issue 01: The Art of MySQL Data Types

This article covers two parts: choosing MySQL column types (which directly affect disk space and performance) and using data types in stored programs such as functions, procedures, and triggers.

1. Numeric Types

Choose the appropriate integer type based on the range of values.

Integers are the most widely used numeric type; they can store numbers, timestamps, or encoded values such as IPv4 addresses.

Example 1

mysql-(ytt/3305)->create table x1( ipaddr int unsigned);

mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.171'));

mysql-(ytt/3305)->alter table x1 add column ipaddr_real varchar(20) generated always as (inet_ntoa(ipaddr)) virtual;

mysql-(ytt/3305)->select * from x1;

Result shows the integer representation of the IP address and its human‑readable form.

Example 2

Compare disk usage of three tables using tinyint, int, and bigint for 20 million rows each.

mysql-(ytt/3305)->CALL `get_schema_records`();

+------------+------------+------------+

|   20000000 |   20000000 |   20000000 |

Result: tinyint uses the least space, bigint the most. Choose the smallest type that fits the range.

2. Floating‑Point / Fixed‑Point Types float and double are floating‑point types; float occupies 4 bytes, double 8 bytes. float(p) where p > 24 is promoted to double. decimal (and its synonym numeric) stores fixed‑point numbers with up to 65 digits of precision.

Use decimal when exact precision is required.

Example 3

mysql-(ytt/3305)->create table y1(f1 float,f2 double,f3 decimal(10,2));

mysql-(ytt/3305)->insert into y1 values (10.2,10.2,10.2);

mysql-(ytt/3305)->insert into y1 values (100.12,100.12,100.12);

mysql-(ytt/3305)->insert into y1 values (1001.12,1001.12,1001.12);

mysql-(ytt/3305)->insert into y1 values (12001.12,12001.12,12001.12);

mysql-(ytt/3305)->insert into y1 values (12001222.12,12001222.12,12001222.12);

mysql-(ytt/3305)->select * from y1;

Only the decimal column stores values precisely; float loses accuracy for large numbers.

After altering f3 to decimal(50,10) and inserting a very large value, the decimal column still retains full precision.

mysql-(ytt/3305)->alter table y1 modify f3 decimal(50,10);

mysql-(ytt/3305)->insert into y1 values (123456789010.1234567,123456789010.1234567,123456789010.1234567);

mysql-(ytt/3305)->select * from y1;

3. String Types char(N) stores fixed‑length strings; varchar(N) stores variable‑length strings up to N characters, using only the actual length plus a length byte. Storage size depends on character set.

Example 4

mysql-(ytt/3305)->create table t4 (c1 char(20),c2 varchar(20));

mysql-(ytt/3305)->set @a = "我是傻傻的小月亮！！！！";

mysql-(ytt/3305)->insert into t4 values (@a,@a);

mysql-(ytt/3305)->select * from t4;

Query shows char occupies more bytes than varchar for the same content.

Use char for fixed‑length values, varchar for variable‑length.

4. Date/Time Types

MySQL provides date, time, datetime, timestamp, and year. Storage sizes: year 1 byte, date 3 bytes, time / timestamp 3 bytes (without fractional seconds), datetime 8 bytes.

Guidelines:

If the range may exceed the 2038‑01‑19 timestamp limit, prefer datetime.

Store a separate year column when you need to query by year frequently.

Store date and time separately if you often filter on one component.

Use native temporal types for millisecond precision instead of strings.

Example 5

mysql-(ytt/3305)->create table t5 (c1 date,c2 datetime(3),c3 timestamp(3),c4 time(3),c5 year);

mysql-(ytt/3305)->set @a='2018-03-25 12:22:33.342';

mysql-(ytt/3305)->insert into t5 values (date(@a), @a,@a,time(@a),year(@a));

mysql-(ytt/3305)->select * from t5;

Virtual column can be used to generate year on the fly.

mysql-(ytt/3305)->alter table t5 drop c5, add c5 year generated always as (year(c1)) virtual;

5. Binary Types binary(N) and varbinary(N) are the binary equivalents of char and varchar. They store raw bytes; character set and collation do not apply.

Example 6

mysql-(ytt/3305)->create table t6 (c1 binary(28),c2 varbinary(28));

mysql-(ytt/3305)->set @a = "我是傻傻的小月亮!!!!";

mysql-(ytt/3305)->insert into t6 values (@a,@a);

mysql-(ytt/3305)->select * from t6;

6. Bit Type bit(M) stores up to 64 bits. Useful for boolean or flag fields. When bit(8) stores a single bit, it is left‑padded with zeros.

Example 7

mysql-(ytt/3305)->create table c1(gender bit(1));

mysql-(ytt/3305)->insert into c1 values (b'0');

mysql-(ytt/3305)->insert into c1 values (b'1');

mysql-(ytt/3305)->select gender+0 as f1 from c1;

Result shows 0 and 1 as decimal values.

Bit columns can also be cast or converted with conv().

mysql-(ytt/3305)->select conv(gender,16,10) as gender from c1 where gender = b'1';

7. Enum Type enum stores one value from a predefined list. It occupies 1–2 bytes and is stored as an index starting at 1. Sorting is based on the index, not the string value.

Example 8

mysql-(ytt/3305)->create table t7(c1 enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));

mysql-(ytt/3305)->insert into t7 values (1);   -- inserts 'mysql'

mysql-(ytt/3305)->insert into t7 values ('postgresql');

mysql-(ytt/3305)->select * from t7 order by c1;

Rows are returned in the order defined by the enum list.

8. Set Type set is similar to enum but can store multiple values simultaneously, up to 64 distinct elements. Internally it uses a 64‑bit bitmap.

Example 9

mysql-(ytt/3305)->create table c7(c1 set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));

-- Insert all 2^7 combinations using a recursive CTE (omitted for brevity)

mysql-(ytt/3305)->select * from c7 where c1 = 'mysql';

Both numeric index and element name can be used in queries.

9. Data Types in Stored Functions

Session variables have weak typing and can change type based on assigned values. Declared variables have explicit types.

Example 10

DELIMITER $$
CREATE DEFINER=`ytt`@`127.0.0.1` FUNCTION `ytt_sample_data_type`(`f1` INT, `f2` INT) RETURNS varchar(100) CHARSET latin1
NO SQL
BEGIN
  DECLARE v_tmp bigint DEFAULT 0;
  SET v_tmp = f1 * f2;
  SET @v_result = CONCAT('The result is: ''',v_tmp,'''.');
  RETURN @v_result;
END$$
DELIMITER ;

SELECT ytt_sample_data_type(1111,222) AS result;

Returns: "The result is: '246642'."

Conclusion

This article introduced the basic MySQL data types and demonstrated their usage with clear examples. In practice, choose the most appropriate type for each column—avoid over‑allocating space (e.g., prefer varchar(100) over varchar(1000)) to achieve better storage efficiency and query performance.