我正在一个典型的LAMP堆栈（L=OS X）上创建一个从命令行运行的PHP脚本，并且在获取特殊字符以正确记录在数据库中时遇到了很多问题。
此脚本递归扫描目录，并将完整路径插入到MySQL数据库表中。我已经做了很多关于如何将特殊字符写入MySQL的研究，但是它们显示为?字符。
代码如下：
<?PHP
ini_set('default_charset', 'UTF-8');
$link = mysql_connect('localhost', '--USER--', '--PASSWORD--');
mysql_set_charset('utf8',$link);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
if(!mysql_select_db("files")) {
die('Could not connect: ' . mysql_error());
}
mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER SET utf8");
function startsWith($haystack, $needle) {
return $needle === "" || strrpos($haystack, $needle, -strlen($haystack)) !== FALSE;
}
function getDirContents($dir, &$results = array()) {
$files = scandir($dir);
foreach($files as $key => $value) {
$path = realpath($dir.DIRECTORY_SEPARATOR.$value);
if(startsWith($path,'/Volumes/Macintosh HD/')) {
unset($files[$key]);
} else if(!is_dir($path) && !startsWith($value,'.') && startsWith($path,'/Volumes/')) {
$results[] = $path;
$query="INSERT IGNORE INTO files (path,dir) VALUES ('$path','0')";
mysql_query($query);
} else if(is_dir($path) && !startsWith($value,'.') && startsWith($path,'/Volumes/')) {
getDirContents($path, $results);
$results[] = $path;
$query="INSERT IGNORE INTO files (path,dir) VALUES ('$path','1')";
mysql_query($query);
}
}
return $results;
}
$directory='/Volumes';
$files=getDirContents($directory);
sort($files);
print_r($files);
?>
有问题的途径是：
/Volumes/Mac Stadium Shuttle 1/DIG2008060702/files/Susan-Jürgen.dvdproj/Contents/PkgInfo
注意
Jürgen中的umlaut字符。当脚本打印数组中的所有文件时，ü将正确显示。如果我在PHP脚本中添加一行以打印
mysql_query()，将返回以下内容：INSERT IGNORE INTO files (path,dir) VALUES ('/Volumes/Mac Stadium Shuttle 1/DIG2008060702/files/Susan-Jürgen.dvdproj/Contents/PkgInfo','0')
再次正确显示
ü。从MySQL命令行客户机，我
SELECT这个路径：mysql> select * from files where path like '%susan%';
…以及回应：
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----------+------+---------------+
| ID | path | dir | google_id | md5 | deleted_local |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----------+------+---------------+
| 644990 | /Volumes/Mac Stadium Shuttle 1/DIG2008060702/files/Susan-Ju?rgen.dvdproj/Contents/PkgInfo | 0 | NULL | NULL | 0 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----------+------+---------------+
…注意到Jürgen中的
ü显示为u?（Ju？尔根）我努力确保：
php.ini的默认字符集是UTF-8
表的默认字符集是utf8
DB连接定义为utf8连接
我在这个脚本的顶部添加了
phpinfo();（在ini_set()之后），并从CLI运行它。default_charset => UTF-8 => UTF-8显示在响应中。在脚本中连接数据库之后，我添加了
echo mysql_client_encoding($link);并打印了utf8脚本。另外，我还跑了：
mysql> show variables like 'char%';回应：
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.6.24-osx10.8-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+
8 rows in set (0.05 sec)
那么，我做错什么了？
编辑表的结构为：
mysql> DESCRIBE files;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| ID | int(11) unsigned | NO | PRI | NULL | auto_increment |
| path | varchar(510) | YES | UNI | NULL | |
| dir | enum('0','1') | YES | | 0 | |
| google_id | varchar(255) | YES | | NULL | |
| md5 | varchar(255) | YES | | NULL | |
| deleted_local | enum('0','1') | YES | | 0 | |
+---------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
另一个编辑：
mysql> show create table files;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| files | CREATE TABLE `files` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`path` varchar(510) CHARACTER SET latin1 DEFAULT NULL,
`dir` enum('0','1') CHARACTER SET latin1 DEFAULT '0',
`google_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`md5` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`deleted_local` enum('0','1') CHARACTER SET latin1 DEFAULT '0',
PRIMARY KEY (`ID`),
UNIQUE KEY `path` (`path`)
) ENGINE=InnoDB AUTO_INCREMENT=961879 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
最佳答案
如第二次编辑所示，path列具有latin1字符集，即使表默认为utf8。也许你已经改变了现有的桌子？
尝试ALTER TABLE files MODIFY path VARCHAR(510) CHARACTER SET utf8;