MySQL错误码 1366 分析与解决方案

INSERT INTO users (name) VALUES ('张三😊');
-- 错误: Incorrect string value: '\xF0\x9F\x98\x8A' for column 'name'

// 方法1: 设置连接字符集为UTF8MB4
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', $user, $pass);

// 方法2: 执行SET NAMES语句
$pdo->exec("SET NAMES utf8mb4");

// 方法3: 字符过滤（谨慎使用）
$filteredName = preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $name);
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute([$filteredName]);

// 方法4: 使用mb_convert_encoding转换编码
$convertedName = mb_convert_encoding($name, 'UTF-8', 'auto');
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (?)");
$stmt->execute([$convertedName]);

-- 修改数据库字符集
ALTER DATABASE your_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 修改表字符集
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改字段字符集
ALTER TABLE your_table
MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 在连接后立即执行
SET NAMES utf8mb4;

-- 或者在my.cnf中配置
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

-- 修改字段允许存储更多字节
ALTER TABLE your_table
MODIFY column_name VARCHAR(255) CHARACTER SET utf8;

-- 或者使用BLOB/TEXT类型
ALTER TABLE your_table
MODIFY column_name TEXT CHARACTER SET utf8mb4;

# 查看数据库字符集
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'your_database';

# 查看表字符集
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database';

# 查看字段字符集
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

# 查看当前连接字符集
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

# 测试字符集支持
SELECT '😊' AS emoji_test;
SELECT '中文测试' AS chinese_test;

# 查看字符编码
SELECT HEX('😊') AS emoji_hex;
SELECT HEX('中文') AS chinese_hex;