|
/*
$splitChar 字段分隔符
$file 数据文件文件名
$table 数据库表名
$conn 数据库连接
$fields 数据对应的列名
$insertType 插入操作类型，包括INSERT,REPLACE
/
<div class="codetitle"><a style="CURSOR: pointer" data="24665" class="copybut" id="copybut24665" onclick="doCopy('code24665')"> 代码如下:<div class="codebody" id="code24665">
<?php
/*
$splitChar 字段分隔符
$file 数据文件文件名
$table 数据库表名
$conn 数据库连接
$fields 数据对应的列名
$insertType 插入操作类型，包括INSERT,REPLACE
/
function loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields=array(),$insertType='INSERT'){
if(empty($fields)) $head = "{$insertType} INTO {$table} VALUES('";
else $head = "{$insertType} INTO {$table}(".implode(',',$fields).") VALUES('"; //数据头
$end = "')";
$sqldata = trim(file_get_contents($file));
if(preg_replace('/s/i','',$splitChar) == '') {
$splitChar = '/(w+)(s+)/i';
$replace = "$1','";
$specialFunc = 'preg_replace';
}else {
$splitChar = $splitChar;
$replace = "','";
$specialFunc = 'str_replace';
}
//处理数据体，二者顺序不可换，否则空格或Tab分隔符时出错
$sqldata = preg_replace('/(s)(n+)(s)/i',''),('',$sqldata); //替换换行
$sqldata = $specialFunc($splitChar,$replace,$sqldata); //替换分隔符
$query = $head.$sqldata.$end; //数据拼接
if(mysql_query($query,$conn)) return array(true);
else {
return array(false,mysql_error($conn),mysql_errno($conn));
}
}
//调用示例1
require 'db.php';
$splitChar = '|'; //竖线
$file = 'sqldata1.txt';
$fields = array('id','parentid','name');
$table = 'cengji';
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/sqlda ta1.txt
|0|A
|1|B
|1|C
|2|D
-- cengji
CREATE TABLE cengji (
id int(11) NOT NULL AUTO_INCREMENT,
parentid int(11) NOT NULL,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY parentid_name_unique (parentid,name) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1602 DEFAULT CHARSET=utf8
/
//调用示例2
require 'db.php';
$splitChar = ' '; //空格
$file = 'sqldata2.txt';
$fields = array('id','make','model','year');
$table = 'cars';
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/ sqldata2.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
-- cars
CREATE TABLE cars (
id int(11) NOT NULL AUTO_INCREMENT,
make varchar(16) NOT NULL,
model varchar(16) DEFAULT NULL,
year varchar(16) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
/
//调用示例3
require 'db.php';
$splitChar = ' '; //Tab
$file = 'sqldata3.txt';
$fields = array('id','year');
$table = 'cars';
$insertType = 'REPLACE';
$result = loadTxtDataIntoDatabase($splitChar,$fields,$insertType);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
/ sqldata3.txt
Aston DB19 2009
Aston DB29 2009
Aston DB39 2009
/
//调用示例3
require 'db.php';
$splitChar = ' '; //Tab
$file = 'sqldata3.txt';
$fields = array('id','value');
$table = 'notExist'; //不存在表
$result = loadTxtDataIntoDatabase($splitChar,$fields);
if (array_shift($result)){
echo 'Success!
';
}else {
echo 'Failed!--Error:'.array_shift($result).'
';
}
//附：db.php
/ //注释这一行可全部释放
?>
<?php
static $connect = null;
static $table = 'jilian';
if(!isset($connect)) {
$connect = mysql_connect("localhost","root","");
if(!$connect) {
$connect = mysql_connect("localhost","Zjmainstay","");
}
if(!$connect) {
die('Can not connect to database.Fatal error handle by /test/db.php');
}
mysql_select_db("test",$connect);
mysql_query("SET NAMES utf8",$connect);
$conn = &$connect;
$db = &$connect;
}
?>
//*/
数据表结构
<div class="codetitle"><a style="CURSOR: pointer" data="16379" class="copybut" id="copybut16379" onclick="doCopy('code16379')"> 代码如下:<div class="codebody" id="code16379">
-- 数据表结构：
-- 100000_insert,1000000_insert
CREATE TABLE 100000_insert (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
100000 （10万）行插入：Insert 100000_line_data use 2.5534288883209 seconds
1000000（100万）行插入：Insert 1000000_line_data use 19.677318811417 seconds
//可能报错：MySQL server has gone away
//解决：修改my.ini/my.cnf max_allowed_packet=20M
作者：Zjmainstay （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
