MySQL提供了多种数据类型，用于定义表中列的数据格式。选择合适的数据类型不仅可以提高查询性能，还能确保数据的完整性和准确性。

一、数值类型

数值类型用于存储整数、浮点数和定点数。根据精度和范围的不同，数值类型可以分为以下几类：

1、整数类型

示例：

CREATE TABLE numbers (id INT AUTO_INCREMENT PRIMARY KEY,tiny_num TINYINT,small_num SMALLINT UNSIGNED,     // 默认是有符范围，UNSIGNED指定为无符medium_num MEDIUMINT,big_num BIGINT
);

说明：
默认的数据类型都是有符号的，如果想要定义为无符号的数据类型，需要加上UNSIGNED关键字，示例如上。

2、浮点数类型

示例：

CREATE TABLE measurements (id INT AUTO_INCREMENT PRIMARY KEY,float_value FLOAT,double_value DOUBLE,decimal_value DECIMAL(10, 2)  -- 10位总长度，2位小数
);

二、字符串类型

字符串类型用于存储文本数据。根据存储方式和长度的不同，字符串类型可以分为固定长度和可变长度两种。

1、定长字符串类型

示例：

CREATE TABLE names (id INT AUTO_INCREMENT PRIMARY KEY,first_name CHAR(50),last_name CHAR(50)
);

2、变长字符串类型

示例：

CREATE TABLE articles (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255),content TEXT,short_description TINYTEXT
);

三、日期和时间类型

日期和时间类型用于存储与时间相关的数据。MySQL提供了多种日期和时间类型，适用于不同的场景。

示例：

CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY,event_date DATE,start_time TIME,end_time TIME,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

四、枚举和集合类型

1、ENUM类型

ENUM类型用于存储一组预定义的值。每个列只能从这些预定义的值中选择一个。

示例：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50),status ENUM('active', 'inactive', 'suspended') DEFAULT 'inactive'
);

2、SET类型

SET类型类似于ENUM，但它允许列中存储多个预定义值的组合。

示例：

CREATE TABLE permissions (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,roles SET('admin', 'editor', 'viewer') NOT NULL
);

五、二进制和BLOB类型

二进制类型用于存储二进制数据，如图片、文件等。BLOB类型是二进制大对象的缩写，适用于存储较大的二进制数据。

示例：

CREATE TABLE images (id INT AUTO_INCREMENT PRIMARY KEY,image_name VARCHAR(255),image_data LONGBLOB
);

六、空间数据类型

空间数据类型用于存储地理空间数据，如点、线、多边形等。MySQL支持OpenGIS规范中的空间数据类型，适用于GIS（地理信息系统）应用。

示例：

CREATE TABLE locations (id INT AUTO_INCREMENT PRIMARY KEY,location POINT
);

插入地理坐标：

INSERT INTO locations (location) VALUES (ST_PointFromText('POINT(120.7 31.2)'));

运行结果：

查询坐标：

select * from locations；

运行结果：

七、数据类型总结

MySQL提供了丰富的数据类型，涵盖了数值、字符串、日期时间、枚举、二进制和空间数据等多种类型。选择合适的数据类型不仅有助于优化存储空间，还能提高查询性能并确保数据的完整性。根据你的具体需求，选择最适合的数据类型来设计表结构。