在PHP项目开发中，会员数据往往包含很多动态变化的扩展信息，比如会员偏好、收货地址列表、个性化配置等，传统的固定字段表结构很难适配这类多变的存储需求。MySQL从5.7版本开始原生支持JSON数据类型，结合PHP的JSON处理函数，可以非常高效地完成这类数据的存储和更新操作。
环境准备与数据表设计
首先需要确保MySQL版本在5.7及以上，PHP版本建议7.0及以上，同时开启PDO或者mysqli扩展用于数据库操作。接下来设计会员表，除了基础的固定字段外，添加一个JSON类型的字段用于存储扩展数据。
-- 创建会员表 CREATE TABLE `member` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL COMMENT '会员账号', `phone` varchar(20) NOT NULL COMMENT '手机号', `extra_data` json DEFAULT NULL COMMENT '会员扩展JSON数据', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PHP连接MySQL数据库
使用PDO方式连接数据库，这种方式支持预处理，能有效避免SQL注入风险。以下是基础的连接代码示例：
<?php
// 数据库配置
$db_host = '127.0.0.1';
$db_name = 'test_db';
$db_user = 'root';
$db_pass = 'root';
try {
// 创建PDO连接
$pdo = new PDO(
"mysql:host=$db_host;dbname=$db_name;charset=utf8mb4",
$db_user,
$db_pass,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
echo "数据库连接成功";
} catch (PDOException $e) {
die("数据库连接失败：" . $e->getMessage());
}
?>
存储JSON格式会员数据
存储数据时，需要先将PHP数组转换为JSON字符串，再插入到MySQL的JSON字段中。以下是新增会员并存储扩展数据的示例：
<?php
// 会员基础信息
$username = 'test_user';
$phone = '13800138000';
// 会员扩展数据，数组形式
$extraData = [
'nickname' => '测试会员',
'avatar' => 'https://ipipp.com/avatar/default.jpg',
'preferences' => ['sports', 'music'],
'address_list' => [
['province' => '广东', 'city' => '深圳', 'detail' => '南山区xx路'],
['province' => '广东', 'city' => '广州', 'detail' => '天河区xx街']
]
];
// 转换为JSON字符串，中文不转义
$extraJson = json_encode($extraData, JSON_UNESCAPED_UNICODE);
// 插入SQL
$sql = "INSERT INTO member (username, phone, extra_data) VALUES (:username, :phone, :extra_data)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':phone', $phone);
$stmt->bindParam(':extra_data', $extraJson);
if ($stmt->execute()) {
echo "会员数据插入成功，新增ID：" . $pdo->lastInsertId();
} else {
echo "会员数据插入失败";
}
?>
查询JSON格式会员数据
MySQL提供了丰富的JSON查询函数，比如JSON_EXTRACT()可以提取JSON字段中的指定内容，->和->>是更简洁的查询语法，其中->>会直接返回解压后的字符串。以下是查询示例：
<?php
// 查询所有会员的账号和昵称（从JSON中提取）
$sql = "SELECT id, username, extra_data->>'$.nickname' as nickname FROM member";
$stmt = $pdo->query($sql);
$memberList = $stmt->fetchAll();
foreach ($memberList as $member) {
echo "会员ID：{$member['id']}，账号：{$member['username']}，昵称：{$member['nickname']}<br>";
}
// 查询偏好包含music的会员
$sql = "SELECT * FROM member WHERE JSON_CONTAINS(extra_data->'$.preferences', '"music"')";
$stmt = $pdo->query($sql);
$musicMembers = $stmt->fetchAll();
foreach ($musicMembers as $member) {
// 将JSON字符串转回PHP数组
$extra = json_decode($member['extra_data'], true);
echo "偏好包含music的会员账号：{$member['username']}，偏好列表：" . implode(',', $extra['preferences']) . "<br>";
}
?>
更新JSON格式会员数据
更新JSON数据有两种常见场景，一种是整体替换整个JSON字段，另一种是只更新JSON中的某个字段或者新增字段，MySQL的JSON_SET()函数可以实现部分更新。
整体替换JSON数据
<?php
// 整体替换ID为1的会员扩展数据
$newExtraData = [
'nickname' => '更新后的昵称',
'avatar' => 'https://ipipp.com/avatar/new.jpg',
'preferences' => ['reading'],
'address_list' => []
];
$newExtraJson = json_encode($newExtraData, JSON_UNESCAPED_UNICODE);
$sql = "UPDATE member SET extra_data = :extra_data WHERE id = 1";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':extra_data', $newExtraJson);
if ($stmt->execute()) {
echo "JSON数据整体更新成功";
} else {
echo "JSON数据整体更新失败";
}
?>
部分更新JSON字段
<?php
// 只更新ID为1的会员昵称，新增vip_level字段
$sql = "UPDATE member SET extra_data = JSON_SET(extra_data, '$.nickname', '部分更新昵称', '$.vip_level', 2) WHERE id = 1";
$stmt = $pdo->prepare($sql);
if ($stmt->execute()) {
echo "JSON数据部分更新成功";
} else {
echo "JSON数据部分更新失败";
}
?>
删除JSON中的字段
如果需要删除JSON中的某个字段，可以使用JSON_REMOVE()函数：
<?php
// 删除ID为1的会员的vip_level字段
$sql = "UPDATE member SET extra_data = JSON_REMOVE(extra_data, '$.vip_level') WHERE id = 1";
$stmt = $pdo->prepare($sql);
if ($stmt->execute()) {
echo "JSON字段删除成功";
} else {
echo "JSON字段删除失败";
}
?>
常见问题与注意事项
- PHP中
json_encode()默认会把中文转成Unicode编码，需要加上JSON_UNESCAPED_UNICODE参数保证中文正常存储。 - MySQL的JSON字段存储的是有效的JSON格式，如果插入的字符串不符合JSON规范，会直接报错。
- 查询JSON字段时，如果字段不存在会返回NULL，需要做空值判断避免业务逻辑错误。
- 不要频繁对大JSON字段做全量更新，尽量使用
JSON_SET()等函数做部分更新，减少数据IO开销。