mysql workbench导入乱码_Mysql Workbench 导入 DBDesigner 乱码解决 办法

１.直接用程序　解释xml文件,将两个编码转成GBK中文字符,再将这些字符转成UTF-8;最后将结果存入 dict表中

php代码　如下:

header("Content-Type: text/html; charset=utf-8");

$obj = simplexml_load_file('/home/bob/Documents/document/project/license/license.xml');

$mysqli = new mysqli("localhost", "root", "123456", "license");

$mysqli->query('set names utf8;');

/* check connection */

if ($mysqli->connect_errno) {

printf("Connect failed: %s\n", $mysqli->connect_error);

exit();

}

//echo __FILE__.__LINE__.'

';print_r($obj->METADATA->TABLES->TABLE);exit;

foreach($obj->METADATA->TABLES->TABLE as $tbl){

$tbl_attr = $tbl->attributes();

$tbl_name = (array)$tbl_attr['Tablename'];

$tbl_name = $tbl_name[0];

$tbl_comment = (array)$tbl_attr['Comments'];

$tbl_comment = convertComment($tbl_comment[0]); //iconv('GB2312', 'UTF-8',

$columns = $tbl->COLUMNS->COLUMN;

/* Create table doesn't return a resultset */

$tbl_comment && $mysqli->query("INSERT INTO `dict`(`tbl`,`col`,`comment`) VALUES ('{$tbl_name}','','{$tbl_comment}')") ;

foreach($columns as $col){

$col_attr = $col->attributes();

$col_name = (array)$col_attr['ColName'];

$col_name = $col_name[0];

$col_comment = (array)$col_attr['Comments'];

$col_comment = convertComment($col_comment[0]);

$col_comment && $mysqli->query("INSERT INTO `dict`(`tbl`,`col`,`comment`) VALUES ('{$tbl_name}','{$col_name}','{$col_comment}')") ;

//echo '

';var_dump($tbl_name);var_dump($tbl_comment);var_dump($col_name);var_dump($col_comment);var_dump(convertComment($tbl_comment));var_dump(convertComment($col_comment)); exit;

}

}

function convertComment($str=''){

$str = preg_replace_callback(

'|(\\\\\\d{3}\\\\\\d{3})+|',

create_function(

// single quotes are essential here,

// or alternative escape all $ as \$

'$matches',

'return convertComment2($matches[0]);'

),

$str

);

return $str;

}

function convertComment2($str=''){

// $str = 'daybyday\215\226\193\222\215\180\204\172\163\1860-\206\180\215\226,1-\201\207\176\235\204\236,2-\207\194\176\235\204\236,3-\210\209\215\226';

$arr_str = explode('\\',trim($str,'\\'));

$cnt= count($arr_str);

$str2='';

for($i=0;$i

$str2 .= chr($arr_str[$i]).chr($arr_str[$i+1]);

}

return iconv('GBK', 'UTF-8',$str2);

}

?>

2. 由于DBDesigner没有同步comment,所以根据dict表的comment生成添加comment 的sql语句 ,然后运行这些语句;此时数据库中已经有正确编码的comment了.　;php代码如下:

/*$dir = "/var/www/html/erp/config/joomla15_crawl";

$files1 = scandir($dir);

echo '

';print_r($files1);exit;

exit;

*/

/**

* beimuaihui System

* Copyright(c) 2011-2020 beimuaihui.

* @license http://www.gnu.org/licenses/gpl.html This software Under GPL V3 License

* beimuaihui@gmail.com

* http://code.google.com/p/beimuaihui/

* $Id: index.php 478 2012-03-12 03:40:01Z beimuaihui@gmail.com $

*/

//echo '

';print_r($_POST);print_r($_FILES);exit;

error_reporting(-1);

@ini_set('display_errors', 'true');

@ini_set("memory_limit", "1024M");

@ini_set("max_execution_time", "240");

@ini_set("short_open_tag", "Off");

@ini_set("magic_quotes_gpc", "Off");

@ini_set("magic_quotes_runtime", "Off");

@set_magic_quotes_runtime(0);

@ini_set('display_errors', 1);

date_default_timezone_set('Asia/Shanghai');

session_name(md5($_SERVER['HTTP_HOST']));

session_start();

$dirs = array_filter(glob('/var/www/html/erp/admin/*'), 'is_dir');

include_once 'config/config_local.php';

$aIncludePath = array(BAOGG_ROOT);

$aIncludePath[] = BAOGG_ROOT. 'library/';

$aIncludePath[] = BAOGG_ROOT . 'models/';

set_include_path(implode(PATH_SEPARATOR, $aIncludePath));

require_once 'Zend/Loader/Autoloader.php'; Zend_Loader::registerAutoload();

Zend_Loader::loadFile('smarty/Smarty.class.php');

$autoloader = Zend_Loader_Autoloader::getInstance();

$autoloader->pushAutoloader('Smarty', 'smartyAutoload');

$autoloader->setFallbackAutoloader(true);

Zend_Loader::loadClass("Zend_Registry");

Zend_Loader::loadClass("Baogg_Db");

Zend_Loader::loadClass("Baogg_Controller");

Zend_Loader::loadClass("Baogg_Controller_Plugin");

Zend_Loader::loadClass('Baogg_View_Smarty');

Zend_Loader::loadClass("Zend_Db_Profiler_Firebug");

Zend_Loader::loadClass("Zend_Log");

Zend_Loader::loadClass("Zend_Log_Writer_Firebug");

header('Content-Type: text/html; charset=utf-8');

//preg_match('/"([^"]+)" \]+)\>/is', '"GEN" <22376415@qq.com>', $arr_from);

//echo '

';print_r($arr_from);exit;

$Dict = new Baogg_Db_Table('WDB','dict');

$db = $Dict->getAdapter();

$rs_meta = $db->fetchAll("desc multilang_content");

//echo '

';print_r($rs_meta);exit;

$rs = $Dict->getList();

$sql = '';

foreach($rs as $v){

if(!trim($v['comment'])) {

continue;

}

try{

$rs_meta = $db->fetchAll("desc {$v['tbl']}");

}catch(Exception $e){

echo "# {$v['tbl']} table not exists;\n";

$rs_meta = array();

continue;

}

if(!$v['col']){

$sql .="ALTER TABLE `{$v['tbl']}` COMMENT = '{$v['comment']}';"."\n";

continue;

}

foreach((array)$rs_meta as $v_meta){

if($v_meta['Field'] == $v['col']){

$sql .= "ALTER TABLE `{$v['tbl']}` CHANGE `{$v['col']}` `{$v['col']}` {$v_meta['Type']} ";

if($v_meta['Null'] == 'NO'){

$sql .= ' not ';

}

$sql.=' null ';

if($v_meta['Default']){

if($v_meta['Default'] == 'CURRENT_TIMESTAMP'){

$sql .=" DEFAULT {$v_meta['Default']} ";

}else{

$sql .= " DEFAULT '{$v_meta['Default']}' ";

}

}

$sql .=" comment '{$v['comment']}';"."\n";

}

}

}

echo $sql;exit;

3.将mysql workbench导入 dbdesigner,此时　comment还全部是乱码;　先将所有表转成myisam;这样就不会删除 表之间　关系;

4.运行mysql workbench Model中的 Database->Sync .. Model -> Update Model;　mysql workbench中的中文comment全部会被　更新成正确编码的comment.