金融/證券/電商系統常需按拼音首字母檢索名稱（如股票代碼輸入「zggh」匹配「中國工行」）。挑戰在於：多音字產生多種首字母組合、需預計算所有可能組合入庫以支持模糊前綴查詢。
漢字[拼音1,拼音2...]）。py_index (short_code, full_name, original_id)。<?php
declare(strict_types=1);
/**
* 從文本加載拼音映射：返回 [漢字 => [首字母1, 首字母2...]]
*/
function loadPinyinMap(string $filePath): array {
$map = [];
$lines = file($filePath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
foreach ($lines as $line) {
// 格式假設：漢字[pin1,pin2] 或 漢字 pin1 pin2
if (preg_match('/^(\p{Han}+)\s*[\[\(]?\s*([^\]\)]+)\s*[\]\)]?$/u', $line, $m)) {
$char = $m[1];
$pinyins = preg_split('/[\s,]+/', $m[2]);
$initials = [];
foreach ($pinyins as $py) {
$py = trim($py);
if ($py !== '') {
$initials[] = mb_strtoupper(mb_substr($py, 0, 1));
}
}
if ($initials) {
$map[$char] = array_unique($initials);
}
}
}
return $map;
}
/**
* 笛卡爾積生成所有組合
* @param array $sets
* @return string[]
*/
function cartesianProduct(array $sets): array {
$result = [''];
foreach ($sets as $set) {
$newResult = [];
foreach ($result as $prefix) {
foreach ($set as $suffix) {
$newResult[] = $prefix . $suffix;
}
}
$result = $newResult;
}
return $result;
}
/**
* 獲取名稱的所有首字母組合
*/
function getNameInitials(string $name, array $pinyinMap): array {
$chars = mb_str_split($name); // PHP 8.1+ 支援多字節分割
$letterSets = [];
foreach ($chars as $char) {
$letterSets[] = $pinyinMap[$char] ?? [mb_strtoupper($char)]; // 非漢字保留原字符
}
return array_unique(cartesianProduct($letterSets));
}
// --- 主流程 ---
$pinyinMap = loadPinyinMap(__DIR__ . '/pinyin_dict.txt'); // 需自備字典
$pdo = new PDO('mysql:host=127.0.0.1;dbname=finance;charset=utf8mb4', 'user', 'pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
// 假設源表 stocks(id, symbol, name)
$stmt = $pdo->query('SELECT id, symbol, name FROM stocks');
$insertSql = 'INSERT IGNORE INTO py_index (short_code, full_name, symbol, stock_id) VALUES (?, ?, ?, ?)';
$insertStmt = $pdo->prepare($insertSql);
$batch = [];
$batchSize = 500;
$count = 0;
while ($row = $stmt->fetch()) {
$initials = getNameInitials($row['name'], $pinyinMap);
foreach ($initials as $code) {
$batch[] = [$code, $row['name'], $row['symbol'], $row['id']];
if (count($batch) >= $batchSize) {
$pdo->beginTransaction();
foreach ($batch as $params) {
$insertStmt->execute($params);
}
$pdo->commit();
$count += count($batch);
$batch = [];
echo "已處理 $count 條索引\n";
}
}
}
// 剩餘批次
if ($batch) {
$pdo->beginTransaction();
foreach ($batch as $params) $insertStmt->execute($params);
$pdo->commit();
$count += count($batch);
}
echo "完成！共生成 $count 條拼音索引記錄。\n"; pinyin_dict.txt)中 [zhong,zhong4]
國 [guo,guo2]
行 [hang,hang2,xing,xing2]
銀 [yin,yin2]
工 [gong,gong1]
商 [shang,shang1]
... (需覆蓋常用漢字 3500+，可從開源項目導出，如 pinyin4j、overtrue/pinyin 數據)CREATE TABLE `py_index` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`short_code` VARCHAR(20) NOT NULL COMMENT '首字母組合，如 ZGYH',
`full_name` VARCHAR(100) NOT NULL COMMENT '原始全稱',
`symbol` VARCHAR(20) DEFAULT NULL COMMENT '股票代碼等業務標識',
`stock_id` BIGINT UNSIGNED NOT NULL COMMENT '關聯主表 ID',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code_name` (`short_code`, `full_name`), -- 防重複
KEY `idx_short_code` (`short_code`) -- 前綴模糊查詢
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 用戶輸入 "zg" 即可匹配 "中國工商銀行"、"張國榮" 等
SELECT * FROM py_index WHERE short_code LIKE 'ZG%' LIMIT 20;overtrue/pinyin Composer 包直接在 PHP 端調用 Pinyin::getInitials()，無需維護文本字典。INSERT IGNORE / ON DUPLICATE KEY UPDATE，避免主鍵衝突報錯。
