php同步mysql两个数据库中表的数据

时间：2021-07-09 13:25:40
分别创建两个数据库和两张表
study库-zone表
teaching库-area表

//****SQL脚本****//

1.创建teaching数据库area数据表
create database teaching;

CREATE TABLE  `area` ( 

 `id` int(11) NOT NULL AUTO_INCREMENT,

  `areaID` varchar(50) CHARACTER SET utf8 DEFAULT NULL, 

 `area` varchar(60) CHARACTER SET utf8 DEFAULT NULL,

  `father` varchar(6) CHARACTER SET utf8 DEFAULT NULL, 

 PRIMARY KEY (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3187 ;

　　

2.给area表中添加数据

INSERT INTO `area` (`id`, `areaID`, `area`, `father`) VALUES

(2759, '610101', '市辖区', '610100'),(2760, '610102', '新城区', '610100'),

(2761, '610103', '碑林区', '610100'),(2762, '610104', '莲湖区', '610100'),

(2763, '610111', '灞桥区', '610100'),(2764, '610112', '未央区', '610100'),

(2765, '610113', '雁塔区', '610100'),(2766, '610114', '阎良区', '610100'),

(2767, '610115', '临潼区', '610100'),(2768, '610116', '长安区', '610100'),

(2769, '610122', '蓝田县', '610100');

　　

3.创建study数据库zone数据表

create database study;

CREATE TABLE `zone` ( 

 `id` int(11) NOT NULL AUTO_INCREMENT, 

`areaID` varchar(50) CHARACTER SET utf8 DEFAULT NULL,

  `area` varchar(60) CHARACTER SET utf8 DEFAULT NULL, 

`father` varchar(6) CHARACTER SET utf8 DEFAULT NULL,

  PRIMARY KEY (`id`)) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3187 ;

　　

php文件执行数据表同步

<?php

$conn = mysqli_connect('localhost', 'root', '', 'study');

$delSql="dalete from `zone`";

$rel_del = mysqli_query($conn,$delSql);

$sql = "insert into study.zone(`id`,`areaID`,`area`,`father`) select `id`,`areaID`,`area`,`father` from teaching.area order by id asc";

$result = mysqli_query($conn,$sql);

if($result){

　　echo "<font color='green'>恭喜恭喜，数据同步成功</font>";

}else{

　　echo "<font color='red'>对不起，数据同步出错，请检查!</font>";

}

?>

　　

相关文章