MySQL EAV模型：减少表创建，提升数据管理效率
liang
|
199
|
|
0
|
MYSQL学习笔记,技术分享,数据库相关

1790 字
|
16 分钟
阅读量: 7

在现代业务环境中，数据管理系统的灵活性至关重要。传统关系型数据库的固定表结构在面对动态变化的需求时，往往显得僵硬且效率低下。本文介绍了一种基于MySQL的实体-属性-值（EAV）模型，旨在通过通用表结构支持多种业务数据的存储和管理，减少表创建需求，并提升扩展性。

新业务的优势

假设我们要存储用户信息（如用户名、密码、电话），传统方法是为用户创建一个表：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    password VARCHAR(50),
    phone VARCHAR(20)
);

但如果需要新增属性（如“地址”），必须修改表结构。

EAV模型的核心在于通过少量通用表存储多种业务数据，具体优势包括：

  1. 灵活性：无需修改表结构即可添加新属性，适应业务变化。例如，新增用户属性如“地址”只需插入common_data_keys，无需改表。
  2. 减少表管理：传统方法可能需要为每种实体（如用户、订单）创建独立表，EAV模型通过common_data_area和相关表统一管理，降低数据库维护复杂度。
  3. 高效数据处理：支持多种数据类型（如VARCHAR、TEXT、INT等），并通过common_data_group_key_values和common_data_group_key_large_values分离存储小和大文本数据，优化查询性能。

旧业务的不足

传统方法通常为每种实体类型创建固定列的表，例如用户表包含用户名、密码等列。其不足包括：

  1. 刚性：新增属性（如用户偏好）需修改表结构，可能涉及停机或数据迁移，影响业务连续性。
  2. 数据冗余：不同实体共享属性时，可能重复定义，增加存储开销。
  3. 可变属性处理困难：某些实体可能有额外属性（如产品有颜色，用户无），导致表中出现大量NULL值，或需创建额外表，查询复杂化。

推荐新业务的原因

EAV模型通过以下方式解决旧业务问题：

  • 灵活性：新增属性只需在common_data_keys中定义，无需改表，适合快速迭代的业务需求。
  • 统一管理：通过common_data_area分类数据，common_data_group存储实例，统一处理多种数据类型，降低维护成本。
  • 扩展性：支持动态业务场景，如电商平台需存储用户、商品等多类型数据，EAV模型可扩展性强。
  • 查询优化：通过视图封装复杂查询，应用层可简单SELECT，减少SQL拼接，提升开发效率。

表创建过程

根据提供的SQL，创建了五个表，具体如下：

DROP TABLE IF EXISTS common_data_area;
CREATE TABLE IF NOT EXISTS common_data_area
(
	common_data_area_id INT                     NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '共用数据区域Id',
	area_name                VARCHAR(32)  DEFAULT '' NOT NULL COMMENT '区域名称',
	description         VARCHAR(128) DEFAULT '' not null COMMENT '描述',
	updated_at          INT          DEFAULT 0  NULL COMMENT '修改时间',
	created_at          INT          DEFAULT 0  NOT NULL COMMENT '创建时间',
	deleted_at          INT          DEFAULT 0  NULL COMMENT '删除时间'
) COMMENT '共用数据区域' COLLATE utf8mb4_unicode_ci CHARACTER SET = utf8mb4;
ALTER TABLE common_data_area
	ADD INDEX area_name ( area_name ),
	ADD INDEX deleted_at ( deleted_at );





DROP TABLE IF EXISTS common_data_keys;
CREATE TABLE IF NOT EXISTS common_data_keys
(
	common_data_keys_id  BIGINT                     NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '共用数据键Id',
	common_data_area_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据区域Id',
	common_data_key      VARCHAR(52)  DEFAULT '' NOT NULL COMMENT '共用数据键名',
	common_data_storage_type TINYINT(2)   DEFAULT 0  NOT NULL COMMENT '共用数据数据存储的类型（0：VARCHAR-字符串、1：TEXT-长文本数据、2：LONGTEXT-长文本数据、3：TINYINT-小整数值、4：INT-大整数值、5：BIGINT-极大数值、6：DOUBLE-浮点数值、7：DECIMAL-小数值、8：BOOLEAN-布尔值、9：DATETIME-日期时间、10：TIMESTAMP-时间戳）',
	description          VARCHAR(128) DEFAULT '' NOT NULL COMMENT '描述',
	updated_at           INT          DEFAULT 0  NULL COMMENT '修改时间',
	created_at           INT          DEFAULT 0  NOT NULL COMMENT '创建时间',
	deleted_at           INT          DEFAULT 0  NULL COMMENT '删除时间'
) COMMENT '共用数据键' COLLATE utf8mb4_unicode_ci
					   CHARACTER SET = utf8mb4;

ALTER TABLE common_data_keys
	add UNIQUE INDEX idx_unique_key (common_data_area_id, common_data_key),
	ADD INDEX common_data_area_id ( common_data_area_id ),
	ADD INDEX common_data_key ( common_data_key ),
	ADD INDEX common_data_storage_type ( common_data_storage_type ),
	ADD INDEX deleted_at ( deleted_at );



DROP TABLE IF EXISTS common_data_group;
CREATE TABLE IF NOT EXISTS common_data_group
(
	common_data_group_id  BIGINT                     NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '共用数据组Id',
	common_data_area_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据区域Id',
	created_admin_id int DEFAULT 0 not null COMMENT '创建操作人Id',
	updated_admin_id int DEFAULT 0 not null COMMENT '更新操作人Id',
	updated_at           INT          DEFAULT 0  NULL COMMENT '修改时间',
	created_at           INT          DEFAULT 0  NOT NULL COMMENT '创建时间',
	deleted_at           INT          DEFAULT 0  NULL COMMENT '删除时间'
) COMMENT '共用数据组' COLLATE utf8mb4_unicode_ci
					   CHARACTER SET = utf8mb4;

ALTER TABLE common_data_group
	add UNIQUE INDEX idx_unique_key (common_data_group_id, common_data_area_id),
	ADD INDEX common_data_area_id ( common_data_area_id ),
	ADD INDEX created_admin_id ( created_admin_id ),
	ADD INDEX updated_admin_id ( updated_admin_id ),
	ADD INDEX deleted_at ( deleted_at );



DROP TABLE IF EXISTS common_data_group_key_values;
CREATE TABLE IF NOT EXISTS common_data_group_key_values
(
	common_data_group_key_values_id  BIGINT                     NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '共用数据组键值Id',
	common_data_area_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据区域Id',
	common_data_group_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据组Id',
	common_data_keys_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据键Id',

	value_varchar    VARCHAR(52)  DEFAULT '' NOT NULL COMMENT '共用数据值',
	value_varchar_index VARCHAR(52)  DEFAULT '' NOT NULL COMMENT '共用数据值索引',

	value_tinyint    TINYINT(3)   DEFAULT 0  NOT NULL COMMENT '共用数据值',
	value_tinyint_index TINYINT(3)   DEFAULT 0  NOT NULL COMMENT '共用数据值索引',

	value_int        INT          DEFAULT 0  NOT NULL COMMENT '共用数据值',
	value_int_index  INT          DEFAULT 0  NOT NULL COMMENT '共用数据值索引',

	value_bigint    BIGINT       DEFAULT 0  NOT NULL COMMENT '共用数据值',
	value_bigint_index BIGINT       DEFAULT 0  NOT NULL COMMENT '共用数据值索引',

	value_double    DOUBLE       DEFAULT NULL COMMENT '共用数据值',

	value_decimal    DECIMAL      DEFAULT NULL   COMMENT '共用数据值',

	value_datetime   DATETIME     DEFAULT NULL COMMENT '共用数据值',

	value_timestamp  TIMESTAMP    DEFAULT NULL  COMMENT '共用数据值'
) COMMENT '共用数据组各键值' COLLATE utf8mb4_unicode_ci
					   CHARACTER SET = utf8mb4;

ALTER TABLE common_data_group_key_values
	ADD INDEX common_data_area_id ( common_data_area_id ),
	ADD INDEX common_data_group_id ( common_data_group_id ),
	ADD INDEX common_data_keys_id ( common_data_keys_id ),
	ADD INDEX value_varchar_index ( value_varchar_index ),
	ADD INDEX value_tinyint_index ( value_tinyint_index ),
	ADD INDEX value_int_index ( value_int_index ),
	ADD INDEX value_bigint_index ( value_bigint_index );




DROP TABLE IF EXISTS common_data_group_key_large_values;
CREATE TABLE IF NOT EXISTS common_data_group_key_large_values
(
	common_data_group_key_values_id  BIGINT                     NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '共用数据组键值Id',
	common_data_area_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据区域Id',
	common_data_group_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据组Id',
	common_data_keys_id  INT          DEFAULT 0  NOT NULL COMMENT '共用数据键Id',

	value_text	   TEXT         DEFAULT NULL COMMENT '共用数据值',

	value_longtext   LONGTEXT     DEFAULT NULL COMMENT '共用数据值'
) COMMENT '共用数据组各键值' COLLATE utf8mb4_unicode_ci
							 CHARACTER SET = utf8mb4;

ALTER TABLE common_data_group_key_large_values
	ADD INDEX common_data_area_id ( common_data_area_id ),
	ADD INDEX common_data_group_id ( common_data_group_id ),
	ADD INDEX common_data_keys_id ( common_data_keys_id );

索引设计考虑查询效率，如common_data_keys的唯一索引确保键名不重复，deleted_at索引支持软删除查询。

测试数据插入与执行结果

提供了插入用户数据的SQL，执行后生成两组用户数据，具体步骤和结果如下：

1. 插入数据区域

INSERT INTO common_data_area (area_name, description, updated_at, created_at)
VALUES ('users', '存储用户业务数据', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW()));

执行结果：

common_data_area_idarea_namedescriptionupdated_atcreated_atdeleted_at
1users存储用户业务数据174001781017400178100

2. 插入键定义

INSERT INTO common_data_keys (common_data_area_id, common_data_key, common_data_storage_type, description, updated_at, created_at)
VALUES (1, 'username', 0, 'User account', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW())),
       (1, 'password', 0, 'User password', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW())),
       (1, 'phone', 0, 'User phone number', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW())),
       (1, 'email', 0, 'User email', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW()));

执行结果：

common_data_keys_idcommon_data_area_idcommon_data_keycommon_data_storage_typedescriptionupdated_atcreated_atdeleted_at
11username0用户帐号174001799817400179980
21password0用户帐号密码174001799817400179980
31phone0用户手机号码174001799817400179980
41email0用户邮箱174001799817400179980

3. 插入数据组

INSERT INTO common_data_group (common_data_area_id, created_admin_id, updated_admin_id, updated_at, created_at)
VALUES (1, 1, 1, UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW())),
       (1, 2, 2, UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW()));

执行结果：

common_data_group_idcommon_data_area_idcreated_admin_idupdated_admin_idupdated_atcreated_atdeleted_at
1111174001907617400190760
2122174001953017400195300

4. 插入数据值

INSERT INTO common_data_group_key_values (common_data_area_id, common_data_group_id, common_data_keys_id, value_varchar_index)
VALUES (1, 1, 1, 'admin'), (1, 1, 2, '123456789'), (1, 1, 3, '13163870291'), (1, 1, 4, 'admin@gmail.com'),
       (1, 2, 1, 'test'), (1, 2, 2, '987654321'), (1, 2, 3, '13163870292'), (1, 2, 4, 'test@gmail.com');

执行结果：

  • 数据组1：用户名admin，密码123456789，电话13163870291，邮箱 admin@gmail.com。
  • 数据组2：用户名test，密码987654321，电话13163870292，邮箱 test@gmail.com。
common_data_group_key_values_id common_data_area_id common_data_group_id common_data_keys_id value_varchar_index
1 1 1 1 admin
2 1 1 2 123456789
3 1 1 3 13163870291
4 1 1 4 admin@gmail.com
5 1 2 1 test
6 1 2 2 987654321
7 1 2 3 13163870292
8 1 2 4 test@gmail.com

测试不同查询方案与执行结果

提供了三种查询方案，均用于获取数据组及其属性值，具体如下：

1. （方案1）子查询方式

SELECT a.*,
       IFNULL((SELECT b.value_varchar_index FROM common_data_group_key_values AS b WHERE a.common_data_group_id = b.common_data_group_id AND b.common_data_keys_id = 1), '') AS username,
       IFNULL((SELECT b.value_varchar_index FROM common_data_group_key_values AS b WHERE a.common_data_group_id = b.common_data_group_id AND b.common_data_keys_id = 2), '') AS password,
       IFNULL((SELECT b.value_varchar_index FROM common_data_group_key_values AS b WHERE a.common_data_group_id = b.common_data_group_id AND b.common_data_keys_id = 3), '') AS phone,
       IFNULL((SELECT b.value_varchar_index FROM common_data_group_key_values AS b WHERE a.common_data_group_id = b.common_data_group_id AND b.common_data_keys_id = 4), '') AS email
FROM common_data_group AS a
GROUP BY a.common_data_group_id, a.common_data_area_id;

执行结果：

common_data_group_idcommon_data_area_idusernamepasswordphoneemail
11admin12345678913163870291admin@gmail.com
21test98765432113163870292test@gmail.com

分析：通过子查询获取每个键的值，效率较低，适合键数少的情况。

2. （方案2）多左连接方式

SELECT a.*,
       COALESCE(b1.value_varchar_index, '') AS username,
       COALESCE(b2.value_varchar_index, '') AS password,
       COALESCE(b3.value_varchar_index, '') AS phone,
       COALESCE(b4.value_varchar_index, '') AS email
FROM common_data_group AS a
LEFT JOIN common_data_group_key_values AS b1 ON a.common_data_group_id = b1.common_data_group_id AND b1.common_data_keys_id = 1
LEFT JOIN common_data_group_key_values AS b2 ON a.common_data_group_id = b2.common_data_group_id AND b2.common_data_keys_id = 2
LEFT JOIN common_data_group_key_values AS b3 ON a.common_data_group_id = b3.common_data_group_id AND b3.common_data_keys_id = 3
LEFT JOIN common_data_group_key_values AS b4 ON a.common_data_group_id = b4.common_data_group_id AND b4.common_data_keys_id = 4
GROUP BY a.common_data_group_id, a.common_data_area_id;

执行结果：与方案1相同，表中数据一致。
分析：通过多左连接获取值，适合键数固定但查询复杂，性能可能受连接数影响。

3. （方案3）条件聚合方式（推荐）

SELECT a.*
FROM (
    SELECT a.*,
           COALESCE(MAX(CASE WHEN b.common_data_keys_id = 1 THEN b.value_varchar_index END), '') AS username,
           COALESCE(MAX(CASE WHEN b.common_data_keys_id = 2 THEN b.value_varchar_index END), '') AS password,
           COALESCE(MAX(CASE WHEN b.common_data_keys_id = 3 THEN b.value_varchar_index END), '') AS phone,
           COALESCE(MAX(CASE WHEN b.common_data_keys_id = 4 THEN b.value_varchar_index END), '') AS email
    FROM common_data_group AS a
    LEFT JOIN common_data_group_key_values AS b ON a.common_data_group_id = b.common_data_group_id
    GROUP BY a.common_data_group_id, a.common_data_area_id
) AS a;

执行结果：与前两方案相同，表中数据一致。
分析：使用条件聚合（CASE WHEN）简化查询，单次连接后分组，扩展性强，适合键数多或动态变化的场景，推荐使用。

视图整合与简化

为减少SQL拼接，可创建视图封装查询逻辑。例如：

CREATE VIEW view_user_data AS
SELECT a.common_data_group_id,
       a.common_data_area_id,
       COALESCE(MAX(CASE WHEN b.common_data_keys_id = 1 THEN b.value_varchar_index END), '') AS username,
       COALESCE(MAX(CASE WHEN b.common_data_keys_id = 2 THEN b.value_varchar_index END), '') AS password,
       COALESCE(MAX(CASE WHEN b.common_data_keys_id = 3 THEN b.value_varchar_index END), '') AS phone,
       COALESCE(MAX(CASE WHEN b.common_data_keys_id = 4 THEN b.value_varchar_index END), '') AS email
FROM common_data_group AS a
LEFT JOIN common_data_group_key_values AS b ON a.common_data_group_id = b.common_data_group_id
GROUP BY a.common_data_group_id, a.common_data_area_id;
  • 优势：应用层可直接SELECT * FROM user_data;获取数据，减少复杂SQL编写，特别适合动态业务场景。
  • 性能：基于方案3，视图查询效率高，索引支持优化。

总结

EAV模型通过通用表结构实现灵活的数据存储，减少表创建需求，支持多种数据类型和动态属性，适合现代业务需求。相比传统固定表方法，新业务在扩展性和维护性上更具优势。通过视图整合查询，进一步简化应用层操作，提升开发效率。此方法可广泛应用于电商、CRM等需处理多样化数据的场景。

关键引用

暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
						

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇