地址字段数据库设计

地址字段是数据库设计中的常见问题，类型选择直接影响存储效率和查询性能。

基础类型对比

地址相关字段的选择

推荐用 CHAR 的字段

```sql

-- 州代码：固定 2 字符

state_code CHAR(2) NOT NULL

-- 邮编：固定 5 字符

zip_code CHAR(5) NOT NULL

-- 国家代码：固定 2 字符 (ISO)

country_code CHAR(2) DEFAULT 'US'

-- ZIP+4 扩展：固定 4 字符

zip4 CHAR(4) DEFAULT NULL

```

推荐用 VARCHAR 的字段

```sql

-- 街道地址：长度可变

street VARCHAR(200) NOT NULL

-- 城市名：长度可变

city VARCHAR(100) NOT NULL

-- 公寓号：长度可变

unit_number VARCHAR(20) DEFAULT NULL

-- 姓名：长度可变

name VARCHAR(100) NOT NULL

```

性能对比实测

测试环境

存储大小

查询性能

```sql

-- 等值查询

SELECT * FROM addresses WHERE state_code = 'CA' AND zip_code = '94105';

-- CHAR: 12ms

-- VARCHAR: 14ms

-- 差异不大

-- 范围查询

SELECT * FROM addresses WHERE zip_code BETWEEN '90000' AND '99999';

-- CHAR: 156ms

-- VARCHAR: 189ms

-- 差异约 21%

```

实际设计案例

用户地址表

```sql

CREATE TABLE user_addresses (

id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

user_id BIGINT UNSIGNED NOT NULL,

-- 结构化字段

name VARCHAR(100) NOT NULL COMMENT '收件人姓名',

phone VARCHAR(20) NOT NULL COMMENT '联系电话',

-- 地址字段

address_line1 VARCHAR(200) NOT NULL COMMENT '主地址',

address_line2 VARCHAR(200) DEFAULT NULL COMMENT '次要地址',

city VARCHAR(100) NOT NULL COMMENT '城市',

state_code CHAR(2) NOT NULL COMMENT '州代码',

zip_code CHAR(5) NOT NULL COMMENT '邮编',

country_code CHAR(2) DEFAULT 'US' COMMENT '国家代码',

-- 元数据

is_default BOOLEAN DEFAULT FALSE,

is_active BOOLEAN DEFAULT TRUE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-- 索引

INDEX idx_user (user_id),

INDEX idx_zip (zip_code),

INDEX idx_state_zip (state_code, zip_code)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

```

索引设计

单列索引

```sql

-- 常用查询字段

CREATE INDEX idx_zip ON addresses(zip_code);

CREATE INDEX idx_state ON addresses(state_code);

CREATE INDEX idx_city ON addresses(city);

```

复合索引

```sql

-- 按州+邮编查询

CREATE INDEX idx_state_zip ON addresses(state_code, zip_code);

-- 按城市+州查询

CREATE INDEX idx_city_state ON addresses(city, state_code);

```

前缀索引（长字段）

```sql

-- 街道名只索引前 20 字符

CREATE INDEX idx_street_prefix ON addresses(street(20));

```

数据归一化

反范式：单字段完整地址

```sql

CREATE TABLE addresses_simple (

id BIGINT PRIMARY KEY,

full_address TEXT,

city VARCHAR(100),

state CHAR(2),

zip CHAR(5)

);

```

适用场景：

范式：结构化字段

```sql

CREATE TABLE addresses_structured (

id BIGINT PRIMARY KEY,

street_number VARCHAR(20),

street_name VARCHAR(100),

street_type VARCHAR(20),

unit_type VARCHAR(20),

unit_number VARCHAR(20),

city VARCHAR(100),

state CHAR(2),

zip CHAR(5)

);

```

适用场景：

数据校验约束

CHECK 约束

```sql

CREATE TABLE addresses (

...

state_code CHAR(2) NOT NULL,

zip_code CHAR(5) NOT NULL,

CONSTRAINT chk_state CHECK (state_code REGEXP '^[A-Z]{2}$'),

CONSTRAINT chk_zip CHECK (zip_code REGEXP '^[0-9]{5}$')

);

```

触发器

```sql

DELIMITER //

CREATE TRIGGER trg_addresses_validate

BEFORE INSERT ON addresses

FOR EACH ROW

BEGIN

-- 自动大写

SET NEW.state_code = UPPER(NEW.state_code);

-- 验证州代码

IF NEW.state_code NOT IN ('AL', 'AK', 'AZ', ...) THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Invalid state code';

END IF;

END //

DELIMITER ;

```

字符集选择

utf8 vs utf8mb4

```sql

-- 完整 Unicode 支持

CREATE TABLE addresses (

...

street VARCHAR(200)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

```

建议：

总结

地址字段设计的最佳实践：

掌握这些原则能让你的地址数据存储既高效又可靠。

```