分析mysql表及表字段的字符集

有时候，我们需要分析mysql数据库中，某个库中所有表的字符集，以及所有表的所有字段的字符集，这一是一件很头疼的事，不过，有我本人下面的代码，就能不太费力的进行前面的工作了，，

<?php
$mysql = mysql_connect('172.16.0.80', 'cms', '123456');
if (!$mysql) exit('can not connect the mysql server');

mysql_select_db('information_schema');

$tableSql = 'SELECT  `TABLE_NAME`,`TABLE_COLLATION`
FROM information_schema.`TABLES`
WHERE Table_Schema =  "zb_lpshop"';

$tableQuery = mysql_query($tableSql);

while (($result = mysql_fetch_assoc($tableQuery)) == true)
{
    $tableList[] = $result;
}
//var_dump($tableList);exit;
foreach ($tableList as $key => $val)
{
    foreach ($val as $k => $v)
    {
        $columnSql = 'SELECT COLUMN_NAME,CHARACTER_SET_NAME FROM COLUMNS WHERE table_name = "'.$v.'" and table_schema = "zb_lpshop"';
        
        $columnQuery = mysql_query($columnSql);
        while(($result = mysql_fetch_assoc($columnQuery)) == true)
        {
            $sigleColumnList[] = $result;
        }
        
        $tableList[$key]['columns'] = $sigleColumnList;
    }
}

$str = "";

foreach ($tableList as $kk => $vv)
{    
    $str .= $vv['TABLE_NAME'].',';
    $str .= $vv['TABLE_COLLATION'].',';
    foreach ($vv['columns'] as $kkk => $vvv)
    {
        if (!$vvv['CHARACTER_SET_NAME'])
        {
            $vvv['CHARACTER_SET_NAME'] = 'default';
        }
        
        if ($kkk %2 == 0)
        {
            $str .= "\n\r";
            $str .= " ,";
            $str .= " ,";
            $str .= " ,";
            $str .= " ,";
        }
        
        $str .= $vvv['COLUMN_NAME'].',';
        $str .= $vvv['CHARACTER_SET_NAME'].',';
    }
    $str .= "\n\r";
}

file_put_contents('/home/cms/log/data.csv', $str);
?>