一个支持大文件导入的 ?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140 |
<?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,$file,$table,$conn,$fields);
if (array_shift($result)){
echo 'Success!<br/>';
}else {
echo 'Failed!--Error:'.array_shift($result).'<br/>';
}
/*sqlda ta1.txt
1|0|A
2|1|B
3|1|C
4|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,$file,$table,$conn,$fields);
if (array_shift($result)){
echo 'Success!<br/>';
}else {
echo 'Failed!--Error:'.array_shift($result).'<br/>';
}
/* sqldata2.txt
11 Aston DB19 2009
12 Aston DB29 2009
13 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','make','model','year');
$table = 'cars';
$insertType = 'REPLACE';
$result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields,$insertType);
if (array_shift($result)){
echo 'Success!<br/>';
}else {
echo 'Failed!--Error:'.array_shift($result).'<br/>';
}
/* sqldata3.txt
11 Aston DB19 2009
12 Aston DB29 2009
13 Aston DB39 2009
*/
//调用示例3
require 'db.php';
$splitChar = ' '; //Tab
$file = 'sqldata3.txt';
$fields = array('id','value');
$table = 'notExist'; //不存在表
$result = loadTxtDataIntoDatabase($splitChar,$file,$table,$conn,$fields);
if (array_shift($result)){
echo 'Success!<br/>';
}else {
echo 'Failed!--Error:'.array_shift($result).'<br/>';
}
//附：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;
}
?> |
//*/
复制代码
-- 数据表结构：
-- 100000_insert,1000000_insert ?
1
2
3
4
5
6 |
CREATE TABLE `100000_insert` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parentid` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
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
下载地址:
php 判断上传文件类型 $_files[]['type']值大全
php运行出错如何显示具体错误信息 |
