<?php
$servername = "10.12.10.10";
$username = "dict_user";
$password = "du_123456";
$dbname1 = "data_center";
$dbname2 = "search_word";
$tname1 = "lexicon_main";

$conn = new mysqli($servername, $username, $password);

if ($conn->connect_error) {
    die("Connect failed: " . $conn->connect_errno);
}

$conn->set_charset("utf8");

echo "Connect ok!";

$myfile = fopen("wordfile.txt", "w");

$sql = "SELECT main_id, table_name, field_name, dic_code FROM " . $dbname2.".".$tname1 ;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while ($row=$result->fetch_assoc()) {
        $tname = $row["table_name"];
        $fname = $row["field_name"];
        $dic_code = $row["dic_code"];
        $main_id = $row["main_id"];

        $tsubname = "lexicon_sub_".$dic_code;
        $sql3 = "create table if not exists " . $dbname2 . "." . $tsubname. "(".
            "`sub_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '词库id', ".
            "`main_id` bigint(20) unsigned NOT NULL COMMENT '主表id',".
            "`dic_code` bigint(20) unsigned NOT NULL COMMENT '词性编码',".
            "`lexicon_name` varchar(50) NOT NULL DEFAULT '' COMMENT '词库标准名',".
            "`relative_name` varchar(50) NOT NULL DEFAULT '' COMMENT '相对标准名',".
            "`ename` varchar(100) NOT NULL DEFAULT '' COMMENT '词库英文名',".
            "`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',".
            "`updatetime` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',".
            "`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',".
            "`remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',".
            "`creator` varchar(10) NOT NULL DEFAULT '' COMMENT '创建人',".
            "`editor` varchar(10) NOT NULL DEFAULT '' COMMENT '编辑人',".
            "PRIMARY KEY (`sub_id`),".
            "UNIQUE KEY `uniq_dic_code` (`main_id`,`lexicon_name`)".
        ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='词库子表'";
        if( $conn->query($sql3) !== TRUE) {
            echo "Error : " . $conn->error;
        }

        $sql2 = "SELECT ". $fname . " from ". $dbname1.".".$tname;
        $r2 = $conn->query($sql2);
        if ($r2->num_rows > 0) {
            while ($row2=$r2->fetch_assoc()) {
                $word = $row2[$fname];
                echo "table: " . $tname . " field: ". $fname.  " word: " . $word . "\n";
                fwrite($myfile, $word."\n");


                $sql3 = "insert into ". $dbname2 . ".".$tsubname .  " ( main_id, dic_code, lexicon_name ) values( '" . $main_id . "','" . $dic_code. "','". $word. "')";
                $conn->query($sql3);
            }
        }
        //echo "<br>table_name. ". $row['table_name'] . " field: " . $row["field_name"];
    }
}

fclose(myfile);
$conn->close();

?>