PHP历理 MySQL UTF8 字符检测工具
<?php
/**
* 检测并突出显示不符合 MySQL utf8 标准的字符
*
* @param string $str 待检测的字符串
* @return string 处理后的字符串，不符合的字符会被标红
*/
function highlightNonMysqlUtf8Chars($str) {
$result = '';
$len = mb_strlen($str, 'UTF-8');
for ($i = 0; $i < $len; $i++) {
$char = mb_substr($str, $i, 1, 'UTF-8');
$unicodeCodePoint = mb_ord($char, 'UTF-8');
// MySQL utf8 只支持 U+0000 至 U+FFFF 范围内的字符
if ($unicodeCodePoint > 0xFFFF) {
$result .= '<span class="layui-badge layui-bg-red" title="U+' . dechex($unicodeCodePoint) . '">'
. htmlspecialchars($char)
. '</span>';
} else {
$result .= htmlspecialchars($char);
}
}
return $result;
}
/**
* 获取不符合 MySQL utf8 标准的字符列表
*
* @param string $str 待检测的字符串
* @return array 包含不符合字符信息的数组
*/
function getNonCompatibleChars($str) {
$nonCompatibleChars = [];
$len = mb_strlen($str, 'UTF-8');
for ($i = 0; $i < $len; $i++) {
$char = mb_substr($str, $i, 1, 'UTF-8');
$unicodeCodePoint = mb_ord($char, 'UTF-8');
if ($unicodeCodePoint > 0xFFFF) {
$nonCompatibleChars[] = [
'position' => $i,
'char' => $char,
'unicode' => 'U+' . strtoupper(dechex($unicodeCodePoint)),
'utf8_bytes' => bin2hex(mb_convert_encoding($char, 'UTF-8', 'UTF-8'))
];
}
}
return $nonCompatibleChars;
}
// 处理表单提交
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$inputText = $_POST['input_text'] ?? '';
$processedText = highlightNonMysqlUtf8Chars($inputText);
$nonCompatibleChars = getNonCompatibleChars($inputText);
$hasNonCompatibleChars = !empty($nonCompatibleChars);
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>MySQL UTF8 字符检测工具</title>
<script src="https://cdn.tailwindcss.com"></script>
<link href="https://cdn.jsdelivr.net/npm/font-awesome@4.7.0/css/font-awesome.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/layui@2.8.11/dist/css/layui.min.css">
<script src="https://cdn.jsdelivr.net/npm/layui@2.8.11/dist/layui.min.js"></script>
<style type="text/tailwindcss">
@layer utilities {
.content-auto {
content-visibility: auto;
}
.text-shadow {
text-shadow: 0 1px 2px rgba(0,0,0,0.1);
}
.bg-gradient-blue {
background: linear-gradient(135deg, #3B82F6 0%, #2563EB 100%);
}
}
</style>
</head>
<body class="bg-gray-50">
<div class="layui-container">
<div class="layui-row layui-col-space15 mt-5">
<!-- 左侧内容 -->
<div class="layui-col-md8">
<div class="layui-card">
<div class="layui-card-header bg-gradient-blue text-white text-shadow">
<i class="fa fa-database mr-2"></i>MySQL UTF8 字符检测工具
</div>
<div class="layui-card-body">
<form class="layui-form" method="post" action="">
<div class="layui-form-item">
<label class="layui-form-label">输入文本</label>
<div class="layui-input-block">
<textarea
name="input_text"
placeholder="请输入需要检测的文本..."
class="layui-textarea"
style="min-height: 200px;"
><?= htmlspecialchars($inputText ?? '') ?></textarea>
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<button type="submit" class="layui-btn layui-btn-normal layui-btn-lg">
<i class="fa fa-check-circle mr-2"></i>开始检测
</button>
</div>
</div>
</form>
<?php if (isset($processedText)): ?>
<div class="layui-collapse mt-5" lay-filter="demo">
<div class="layui-colla-item">
<h2 class="layui-colla-title <?= $hasNonCompatibleChars ? 'layui-bg-red' : 'layui-bg-green' ?>">
<i class="fa fa-info-circle mr-2"></i>检测结果
<?= $hasNonCompatibleChars ?
'<span class="layui-badge layui-bg-red ml-2">发现问题</span>' :
'<span class="layui-badge layui-bg-green ml-2">一切正常</span>'
?>
</h2>
<div class="layui-colla-content layui-show">
<div class="layui-code" lay-title="检测结果" lay-skin="notepad">
<?= $processedText ?>
</div>
<?php if ($hasNonCompatibleChars): ?>
<div class="layui-table-box mt-4">
<table class="layui-table" lay-skin="line">
<colgroup>
<col width="100">
<col width="100">
<col width="150">
<col>
</colgroup>
<thead>
<tr>
<th>位置</th>
<th>字符</th>
<th>Unicode 编码</th>
<th>UTF-8 字节</th>
</tr>
</thead>
<tbody>
<?php foreach ($nonCompatibleChars as $charInfo): ?>
<tr>
<td><?= $charInfo['position'] ?></td>
<td><span class="layui-badge layui-bg-red"><?= htmlspecialchars($charInfo['char']) ?></span></td>
<td><?= $charInfo['unicode'] ?></td>
<td><?= $charInfo['utf8_bytes'] ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php endif; ?>
</div>
</div>
</div>
<?php endif; ?>
</div>
</div>
</div>
<!-- 右侧信息 -->
<div class="layui-col-md4">
<div class="layui-card">
<div class="layui-card-header layui-bg-gray">
<i class="fa fa-book mr-2"></i>关于 MySQL UTF8
</div>
<div class="layui-card-body">
<div class="layui-timeline">
<div class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<h3 class="layui-timeline-title">什么是 MySQL UTF8？</h3>
<p>MySQL 的 utf8 编码最多支持 3 字节的 UTF-8 字符，对应 Unicode 范围为 U+0000 至 U+FFFF。</p>
</div>
</div>
<div class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<h3 class="layui-timeline-title">哪些字符需要 utf8mb4？</h3>
<ul class="layui-list layui-list-bullet">
<li>Emoji 表情符号</li>
<li>某些生僻汉字</li>
<li>特殊符号和数学符号</li>
<li>部分亚洲文字</li>
</ul>
</div>
</div>
<div class="layui-timeline-item">
<i class="layui-icon layui-timeline-axis"></i>
<div class="layui-timeline-content layui-text">
<h3 class="layui-timeline-title">如何解决兼容性问题？</h3>
<p>将数据库、表和字段的字符集修改为 <span class="layui-badge">utf8mb4</span>，并确保应用程序使用正确的字符集连接数据库。</p>
</div>
</div>
</div>
</div>
</div>
<div class="layui-card mt-3">
<div class="layui-card-header layui-bg-gray">
<i class="fa fa-lightbulb-o mr-2"></i>使用提示
</div>
<div class="layui-card-body">
<ul class="layui-list layui-list-number">
<li>输入需要检测的文本，点击"开始检测"按钮</li>
<li>系统会标红显示无法在 MySQL utf8 中存储的字符</li>
<li>点击检测结果可查看详细的问题字符列表</li>
<li>建议对包含特殊字符的数据库使用 utf8mb4 编码</li>
</ul>
</div>
</div>
</div>
</div>
<footer class="layui-row mt-5 text-center text-gray-500 text-sm">
<p>© 2025 MySQL UTF8 字符检测工具 | 使用 PHP + Layui 构建</p>
</footer>
</div>
<script>
layui.use(['layer', 'form'], function(){
var layer = layui.layer;
var form = layui.form;
// 初始化代码高亮
layui.code({
elem: '.layui-code'
,title: '检测结果'
,skin: 'notepad'
,about: false
});
// 折叠面板
layui.element.render('collapse');
// 鼠标悬停提示
$('[title]').hover(function(){
layer.tips($(this).attr('title'), this, {
tips: [1, '#3B82F6'],
time: 2000
});
});
});
</script>
</body>
</html>
效果图：
浙公网安备 33010602011771号