在PHP项目开发中，经常会遇到需要存储结构化数据的场景，比如用户偏好设置、商品扩展属性等，这类数据字段不固定，使用传统的关系型字段会导致表结构频繁变动，而JSON字段可以很好地解决这个问题。JSON字段是MySQL 5.7及以上版本支持的原生类型，能够直接存储JSON格式的数据，同时支持对JSON内部内容的查询和修改。
一、JSON字段建表技巧
1. 基础建表语法
创建包含JSON字段的表时，直接使用JSON类型定义字段即可，不需要指定长度，建表语句示例如下：
-- 创建用户扩展信息表，包含JSON字段存储用户偏好
CREATE TABLE user_extend (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
-- JSON字段存储用户偏好，比如主题、通知设置等
preferences JSON,
-- JSON字段存储用户扩展属性，比如自定义标签
extra_info JSON,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2. 建表注意事项
- JSON字段仅支持MySQL 5.7及以上版本，建表前需要确认数据库版本
- 字符集建议使用utf8mb4，避免存储中文或特殊字符时出现乱码
- 如果JSON字段有默认值需求，默认值需要是合法的JSON格式，比如默认空对象可以设置为
'{}' - 不需要给JSON字段单独建立普通索引，如果需要查询JSON内部字段，可以创建生成列索引提升查询效率
3. 生成列索引建表示例
如果经常需要查询JSON字段内部的某个属性，可以通过生成列加索引的方式优化查询：
CREATE TABLE product (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
-- 存储商品扩展属性，比如颜色、尺寸
attrs JSON,
-- 生成列，提取attrs中的color属性
color VARCHAR(20) GENERATED ALWAYS AS (attrs->'$.color') VIRTUAL,
-- 给生成列加索引
INDEX idx_color (color)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
二、PHP添加JSON字段数据
1. 使用PDO方式添加
PHP通过PDO操作数据库时，只需要将数组通过json_encode转换为JSON字符串，再作为参数传入即可：
<?php
// 数据库连接配置
$dsn = 'mysql:host=127.0.0.1;dbname=test;charset=utf8mb4';
$username = 'root';
$password = '123456';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 要存储的用户偏好数据
$preferences = [
'theme' => 'dark',
'notify_email' => true,
'notify_sms' => false
];
// 要存储的扩展信息
$extraInfo = [
'tags' => ['php', 'mysql'],
'level' => 2
];
// 插入SQL，JSON字段直接传入json_encode后的字符串
$sql = 'INSERT INTO user_extend (user_id, preferences, extra_info) VALUES (?, ?, ?)';
$stmt = $pdo->prepare($sql);
$stmt->execute([
1,
json_encode($preferences, JSON_UNESCAPED_UNICODE),
json_encode($extraInfo, JSON_UNESCAPED_UNICODE)
]);
echo '插入成功，新增ID：' . $pdo->lastInsertId();
} catch (PDOException $e) {
echo '操作失败：' . $e->getMessage();
}
?>
2. 使用mysqli方式添加
mysqli方式的操作逻辑和PDO类似，同样需要将数组转为JSON字符串后传入：
<?php
// 数据库连接
$mysqli = new mysqli('127.0.0.1', 'root', '123456', 'test');
if ($mysqli->connect_error) {
die('连接失败：' . $mysqli->connect_error);
}
$mysqli->set_charset('utf8mb4');
// 准备数据
$preferences = [
'theme' => 'light',
'notify_email' => false
];
$preferencesJson = json_encode($preferences, JSON_UNESCAPED_UNICODE);
// 插入数据，注意字符串参数需要加单引号
$sql = "INSERT INTO user_extend (user_id, preferences) VALUES (2, '$preferencesJson')";
if ($mysqli->query($sql)) {
echo '插入成功，新增ID：' . $mysqli->insert_id;
} else {
echo '插入失败：' . $mysqli->error;
}
$mysqli->close();
?>
三、JSON字段的查询与更新
1. 查询JSON字段
查询时可以使用MySQL的JSON函数提取内部属性，PHP接收后通过json_decode转为数组使用：
<?php
// PDO查询示例，查询user_id为1的用户偏好
$stmt = $pdo->prepare('SELECT preferences FROM user_extend WHERE user_id = ?');
$stmt->execute([1]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row) {
// 将JSON字符串转为数组
$preferences = json_decode($row['preferences'], true);
echo '用户主题：' . $preferences['theme'];
}
?>
2. 更新JSON字段
更新JSON字段可以整体替换，也可以只修改内部某个属性，以下是修改内部属性的示例：
<?php // 修改user_id为1的用户偏好中的theme为dark $sql = "UPDATE user_extend SET preferences = JSON_SET(preferences, '$.theme', 'dark') WHERE user_id = 1"; $stmt = $pdo->prepare($sql); $stmt->execute(); echo '更新成功，影响行数：' . $stmt->rowCount(); ?>
四、使用注意事项
- 存储到JSON字段的数据必须是合法的JSON格式，否则会导致插入失败
- JSON字段不适合存储需要频繁关联查询的数据，关联查询还是建议使用传统的关系型字段
- PHP中
json_encode建议加上JSON_UNESCAPED_UNICODE参数，避免中文被转义为Unicode编码 - 不要将过大的JSON数据存入字段，避免影响查询性能，单个JSON字段建议不超过1KB