Mysql Workbench 导入　DBDesigner 乱码解决　办法

最新推荐文章于 2025-01-02 16:00:41 发布

原创最新推荐文章于 2025-01-02 16:00:41 发布 · 1.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#workbench

mysql 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了一种使用PHP从XML文件解析并转换表和列注释的方法，将GBK编码转换为UTF-8，并同步到MySQL数据库。此外，还提供了解决DBDesigner与MySQL Workbench之间注释同步问题的技术方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

　php代码　如下:

<html>
<head>
	<meta http-equiv = " content-type " content = " text/html; charset=utf-8 " />
</head>
<body>

<?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__.'<pre>';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 '<pre>';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<$cnt;$i=$i+2){
    $str2 .=  chr($arr_str[$i]).chr($arr_str[$i+1]);
  }
  return  iconv('GBK', 'UTF-8',$str2);
}
?>

</body>
</html>

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

<?php





/*$dir = "/var/www/html/erp/config/joomla15_crawl";
$files1 = scandir($dir);
echo '<pre>';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 '<pre>';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 '<pre>';print_r($arr_from);exit;


$Dict = new Baogg_Db_Table('WDB','dict');
$db 	= $Dict->getAdapter();

$rs_meta = $db->fetchAll("desc multilang_content");
//echo '<pre>';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.