


php读取txt文件并将数据插入到数据库,
今天测试一个功能,需要往数据库中插入一些原始数据,PM给了一个txt文件,如何快速的将这个txt文件的内容拆分为所要的数组,然后再插入到数据库中?
serial_number.txt的示例内容:
serial_number.txt:
DM00001A11 0116, SN00002A11 0116, AB00003A11 0116, PV00004A11 0116, OC00005A11 0116, IX00006A11 0116,
创建数据表:
create table serial_number( id int primary key auto_increment not null, serial_number varchar(50) not null )ENGINE=InnoDB DEFAULT CHARSET=utf8;
php代码如下:
$conn = mysql_connect('127.0.0.1','root','') or die("Invalid query: " . mysql_error()); mysql_select_db('test', $conn) or die("Invalid query: " . mysql_error()); $content = file_get_contents("serial_number.txt"); $contents= explode(",",$content);//explode()函数以","为标识符进行拆分 foreach ($contents as $k => $v)//遍历循环 { $id = $k; $serial_number = $v; mysql_query("insert into serial_number (`id`,`serial_number`) VALUES('$id','$serial_number')"); }
备注:方法有很多种,我这里是在拆分txt文件为数组后,然后遍历循环得到的数组,每循环一次,往数据库中插入一次。
再给大家分享一个支持大文件导入的
<?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
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读取txt文件组成SQL并插入数据库的代码(原创自Zjmainstay)
- 基于PHP读取TXT文件向数据库导入海量数据的方法

Laravel simplifies handling temporary session data using its intuitive flash methods. This is perfect for displaying brief messages, alerts, or notifications within your application. Data persists only for the subsequent request by default: $request-

The PHP Client URL (cURL) extension is a powerful tool for developers, enabling seamless interaction with remote servers and REST APIs. By leveraging libcurl, a well-respected multi-protocol file transfer library, PHP cURL facilitates efficient execution of various network protocols, including HTTP, HTTPS, and FTP. This extension offers granular control over HTTP requests, supports multiple concurrent operations, and provides built-in security features.

Laravel provides concise HTTP response simulation syntax, simplifying HTTP interaction testing. This approach significantly reduces code redundancy while making your test simulation more intuitive. The basic implementation provides a variety of response type shortcuts: use Illuminate\Support\Facades\Http; Http::fake([ 'google.com' => 'Hello World', 'github.com' => ['foo' => 'bar'], 'forge.laravel.com' =>

Do you want to provide real-time, instant solutions to your customers' most pressing problems? Live chat lets you have real-time conversations with customers and resolve their problems instantly. It allows you to provide faster service to your custom

Laravel's service container and service providers are fundamental to its architecture. This article explores service containers, details service provider creation, registration, and demonstrates practical usage with examples. We'll begin with an ove

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

PHP logging is essential for monitoring and debugging web applications, as well as capturing critical events, errors, and runtime behavior. It provides valuable insights into system performance, helps identify issues, and supports faster troubleshoot

The article discusses adding custom functionality to frameworks, focusing on understanding architecture, identifying extension points, and best practices for integration and debugging.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

WebStorm Mac version
Useful JavaScript development tools

Notepad++7.3.1
Easy-to-use and free code editor

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.