blocks|key|1143889|text|您不能安装phpMyAdmin，gzip文件(这将使其更小)并使用phpMyAdmin导入它吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1143890|编辑：，如果您不能使用phpMyAdmin，您可以使用来自phpMyAdmin的代码。我不确定这个特殊的部分，但它的总体结构很好。|offset|length|style|BOLD|1143891|entityMap^0|0|0|3|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Can't you install phpMyAdmin, gzip the file (which should make it much smaller) and import it using phpMyAdmin?

EDIT: Well, if you can't use phpMyAdmin, you can use the code from phpMyAdmin. I'm not sure about this particular part, but it's generaly nicely structured.

blocks|key|1229721|text|你能用加载数据信息？吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1229722|如果使用SELECT格式将db转储文件格式化为OUTFILE，这正是您所需要的。没有理由让PHP解析任何东西。|1229723|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/load-data.html^0|3|7|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Can you use <a href="http://dev.mysql.com/doc/refman/5.1/en/load-data.html" rel="nofollow noreferrer">LOAD DATA INFILE?</a>

If you format your db dump file using SELECT INTO OUTFILE, this should be exactly what you need. No reason to have PHP parse anything.

blocks|key|1229775|text|已经回答：从PHP内部加载.sql文件也是：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1229776|http://webxadmin.free.fr/article/import-huge-mysql-dumps-using-php-only-342.php|unordered-list-item|1229777|http://www.phpbuilder.com/board/showthread.php?t=10323180|1229778|http://forums.tizag.com/archive/index.php?t-3581.html|1229779|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/147821/loading-sql-files-from-within-php|1|2|3^0|5|E|0|0|0|27|1|0|0|1L|2|0|0|1H|3|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@$A|10|B|11|1|12]]|C|$]]|$1|G|3|H|5|F|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|I|3|J|5|F|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|K|3|-4|5|6|7|1B|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|E]]|T|$5|N|O|P|C|$Q|H]]|U|$5|N|O|P|C|$Q|J]]]]

Already answered: <a href="https://stackoverflow.com/questions/147821/loading-sql-files-from-within-php">Loading .sql files from within PHP</a>
Also:

<ul>
<li><a href="http://webxadmin.free.fr/article/import-huge-mysql-dumps-using-php-only-342.php" rel="nofollow noreferrer">http://webxadmin.free.fr/article/import-huge-mysql-dumps-using-php-only-342.php</a></li>
<li><a href="http://www.phpbuilder.com/board/showthread.php?t=10323180" rel="nofollow noreferrer">http://www.phpbuilder.com/board/showthread.php?t=10323180</a></li>
<li><a href="http://forums.tizag.com/archive/index.php?t-3581.html" rel="nofollow noreferrer">http://forums.tizag.com/archive/index.php?t-3581.html</a></li>
</ul>

blocks|key|1144771|text|你怎么想的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1144772|system("cat+xxx.sql+%7C+mysql+-l+username+database");+|code-block|syntax|javascript|1144773|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

what do you think about:

<pre><code>system("cat xxx.sql | mysql -l username database"); 
</code></pre>

blocks|key|2752321|text|下面是一个对内存友好的函数，它应该能够在单独的查询中拆分一个大文件，而不需要同时打开整个文件()。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2752322|function+SplitSQL($file,+$delimiter+=+';')
{
++++set_time_limit(0);

++++if+(is_file($file)+===+true)
++++{
++++++++$file+=+fopen($file,+'r');

++++++++if+(is_resource($file)+===+true)
++++++++{
++++++++++++$query+=+array();

++++++++++++while+(feof($file)+===+false)
++++++++++++{
++++++++++++++++$query[]+=+fgets($file);

++++++++++++++++if+(preg_match('~'+.+preg_quote($delimiter,+'~')+.+'\s*$~iS',+end($query))+===+1)
++++++++++++++++{
++++++++++++++++++++$query+=+trim(implode('',+$query));

++++++++++++++++++++if+(mysql_query($query)+===+false)
++++++++++++++++++++{
++++++++++++++++++++++++echo+'<h3>ERROR:+'+.+$query+.+'</h3>'+.+"\n";
++++++++++++++++++++}

++++++++++++++++++++else
++++++++++++++++++++{
++++++++++++++++++++++++echo+'<h3>SUCCESS:+'+.+$query+.+'</h3>'+.+"\n";
++++++++++++++++++++}

++++++++++++++++++++while+(ob_get_level()+>+0)
++++++++++++++++++++{
++++++++++++++++++++++++ob_end_flush();
++++++++++++++++++++}

++++++++++++++++++++flush();
++++++++++++++++}

++++++++++++++++if+(is_string($query)+===+true)
++++++++++++++++{
++++++++++++++++++++$query+=+array();
++++++++++++++++}
++++++++++++}

++++++++++++return+fclose($file);
++++++++}
++++}

++++return+false;
}|code-block|syntax|javascript|2752323|我在一个大型的phpMyAdmin+SQL转储上测试了它，它运行得很好。|2752324|一些测试数据：|2752325|CREATE+TABLE+IF+NOT+EXISTS+"test"+(
++++"id"+INTEGER+PRIMARY+KEY+AUTOINCREMENT,
++++"name"+TEXT,
++++"description"+TEXT
);

BEGIN;
++++INSERT+INTO+"test"+("name",+"description")
++++VALUES+(";;;",+"something+for+you+mind;+body;+soul");
COMMIT;

UPDATE+"test"
++++SET+"name"+=+";+"
++++WHERE+"id"+=+1;|2752326|和相应的输出：|2752327|SUCCESS:+CREATE+TABLE+IF+NOT+EXISTS+"test"+(+"id"+INTEGER+PRIMARY+KEY+AUTOINCREMENT,+"name"+TEXT,+"description"+TEXT+);
SUCCESS:+BEGIN;
SUCCESS:+INSERT+INTO+"test"+("name",+"description")+VALUES+(";;;",+"something+for+you+mind;+body;+soul");
SUCCESS:+COMMIT;
SUCCESS:+UPDATE+"test"+SET+"name"+=+";+"+WHERE+"id"+=+1;|2752328|entityMap^0|1B|2|0|0|0|10|0|0|7|0|0|0|7|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|U|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|V|$]]

Here is a memory-friendly function that should be able to split a big file in individual queries without needing to open the whole file at once:

<pre><code>function SplitSQL($file, $delimiter = ';')
{
 set_time_limit(0);

 if (is_file($file) === true)
 {
 $file = fopen($file, 'r');

 if (is_resource($file) === true)
 {
 $query = array();

 while (feof($file) === false)
 {
 $query[] = fgets($file);

 if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1)
 {
 $query = trim(implode('', $query));

 if (mysql_query($query) === false)
 {
 echo '&lt;h3&gt;ERROR: ' . $query . '&lt;/h3&gt;' . "\n";
 }

 else
 {
 echo '&lt;h3&gt;SUCCESS: ' . $query . '&lt;/h3&gt;' . "\n";
 }

 while (ob_get_level() &gt; 0)
 {
 ob_end_flush();
 }

 flush();
 }

 if (is_string($query) === true)
 {
 $query = array();
 }
 }

 return fclose($file);
 }
 }

 return false;
}
</code></pre>

I tested it on a big phpMyAdmin SQL dump and it worked just fine.

<hr>

Some test data:

<pre><code>CREATE TABLE IF NOT EXISTS "test" (
 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
 "name" TEXT,
 "description" TEXT
);

BEGIN;
 INSERT INTO "test" ("name", "description")
 VALUES (";;;", "something for you mind; body; soul");
COMMIT;

UPDATE "test"
 SET "name" = "; "
 WHERE "id" = 1;
</code></pre>

And the respective output:

<pre><code>SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT );
SUCCESS: BEGIN;
SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul");
SUCCESS: COMMIT;
SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1;
</code></pre>

blocks|key|1143971|text|当StackOverflow以XML格式发布每月数据转储时，我编写了MySQL脚本将其加载到MySQL数据库中。我在几分钟内导入了大约2.2GB的XML。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1143972|我的技术是为列值设置带有参数占位符的prepare()+INSERT语句。然后使用XMLReader循环XML元素和execute()我准备的查询，插入参数的值。我之所以选择XMLReader，是因为它是一个流式XML读取器；它增量地读取XML输入，而不是需要将整个文件加载到内存中。|offset|length|style|CODE|1143973|您还可以使用fgetcsv()一次只读取一行CSV文件。|1143974|如果要导入InnoDB表，我建议显式地启动和提交事务，以减少自动提交的开销。我每1000行提交一次，但这是任意的。|1143975|我不打算在这里发布代码(因为StackOverflow的许可策略)，而是用伪代码：|1143976|connect+to+database
open+data+file
PREPARE+parameterizes+INSERT+statement
begin+first+transaction
loop,+reading+lines+from+data+file:+{
++++parse+line+into+individual+fields
++++EXECUTE+prepared+query,+passing+data+fields+as+parameters
++++if+%2B%2Bcounter+%25+1000+==+0,
++++++++commit+transaction+and+begin+new+transaction
}
commit+final+transaction|code-block|syntax|javascript|1143977|用PHP编写这段代码并不是一门高超的科学，而且当使用准备好的语句和显式事务时，它运行得非常快。这些特性在过时的mysql+PHP扩展中不可用，但如果使用米斯里或MySQL，则可以使用它们。|1143978|当数据文件不包括其中一个字段时，我还添加了一些方便的内容，比如错误检查、进度报告和对默认值的支持。|1143979|我用一个abstract+PHP类编写了我的代码，为我需要加载的每个表都提供了子类。每个子类都声明要加载的列，并按名称(如果数据文件为CSV)将它们映射到XML数据文件中的字段。|1143980|entityMap|0|LINK|mutability|MUTABLE|url|http://php.net/manual/en/pdo.prepare.php|1|http://php.net/xmlreader|2|http://php.net/manual/en/pdostatement.execute.php|3|http://us3.php.net/manual/en/function.fgetcsv.php|4|http://php.net/manual/en/book.mysql.php|5|http://php.net/manual/en/book.mysqli.php|6|http://php.net/manual/en/ref.pdo-mysql.php^0|0|I|9|S|6|15|9|1M|9|I|9|0|15|9|1|1M|9|2|0|6|9|6|9|3|0|0|0|0|1J|5|1J|5|4|24|3|5|28|5|6|0|0|4|8|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1J|8|@$D|1K|E|1L|F|G]|$D|1M|E|1N|F|G]|$D|1O|E|1P|F|G]|$D|1Q|E|1R|F|G]]|9|@$D|1S|E|1T|1|1U]|$D|1V|E|1W|1|1X]|$D|1Y|E|1Z|1|20]]|A|$]]|$1|H|3|I|5|6|7|21|8|@$D|22|E|23|F|G]]|9|@$D|24|E|25|1|26]]|A|$]]|$1|J|3|K|5|6|7|27|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|28|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|29|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|2A|8|@$D|2B|E|2C|F|G]]|9|@$D|2D|E|2E|1|2F]|$D|2G|E|2H|1|2I]|$D|2J|E|2K|1|2L]]|A|$]]|$1|U|3|V|5|6|7|2M|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2N|8|@$D|2O|E|2P|F|G]]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|2Q|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]|16|$5|11|12|13|A|$14|17]]|18|$5|11|12|13|A|$14|19]]|1A|$5|11|12|13|A|$14|1B]]|1C|$5|11|12|13|A|$14|1D]]|1E|$5|11|12|13|A|$14|1F]]|1G|$5|11|12|13|A|$14|1H]]]]

When StackOverflow released their monthly data dump in XML format, I wrote PHP scripts to load it into a MySQL database. I imported about 2.2 gigabytes of XML in a few minutes.

My technique is to <a href="http://php.net/manual/en/pdo.prepare.php" rel="nofollow noreferrer"><code>prepare()</code></a> an <code>INSERT</code> statement with parameter placeholders for the column values. Then use <a href="http://php.net/xmlreader" rel="nofollow noreferrer"><code>XMLReader</code></a> to loop over the XML elements and <a href="http://php.net/manual/en/pdostatement.execute.php" rel="nofollow noreferrer"><code>execute()</code></a> my prepared query, plugging in values for the parameters. I chose XMLReader because it's a streaming XML reader; it reads the XML input incrementally instead of requiring to load the whole file into memory.

You could also read a CSV file one line at a time with <a href="http://us3.php.net/manual/en/function.fgetcsv.php" rel="nofollow noreferrer"><code>fgetcsv()</code></a>.

If you're inporting into InnoDB tables, I recommend starting and committing transactions explicitly, to reduce the overhead of autocommit. I commit every 1000 rows, but this is arbitrary.

I'm not going to post the code here (because of StackOverflow's licensing policy), but in pseudocode:

<pre><code>connect to database
open data file
PREPARE parameterizes INSERT statement
begin first transaction
loop, reading lines from data file: {
 parse line into individual fields
 EXECUTE prepared query, passing data fields as parameters
 if ++counter % 1000 == 0,
 commit transaction and begin new transaction
}
commit final transaction
</code></pre>

Writing this code in PHP is not rocket science, and it runs pretty quickly when one uses prepared statements and explicit transactions. Those features are not available in the outdated <a href="http://php.net/manual/en/book.mysql.php" rel="nofollow noreferrer"><code>mysql</code></a> PHP extension, but you can use them if you use <a href="http://php.net/manual/en/book.mysqli.php" rel="nofollow noreferrer">mysqli</a> or <a href="http://php.net/manual/en/ref.pdo-mysql.php" rel="nofollow noreferrer">PDO_MySQL</a>.

I also added convenient stuff like error checking, progress reporting, and support for default values when the data file doesn't include one of the fields. 

I wrote my code in an <code>abstract</code> PHP class that I subclass for each table I need to load. Each subclass declares the columns it wants to load, and maps them to fields in the XML data file by name (or by position if the data file is CSV).

blocks|key|1229895|text|单页PHPMyAdmin+-+Adminer+-只是一个PHP脚本文件。检查：http://www.adminer.org/en/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1229896|entityMap|0|LINK|mutability|MUTABLE|url|http://www.adminer.org/en/^0|13|Q|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Single page PHPMyAdmin - Adminer - Just one PHP script file.
check : <a href="http://www.adminer.org/en/" rel="noreferrer">http://www.adminer.org/en/</a>

blocks|key|1144855|text|我也遇到了同样的问题。我使用正则表达式解决了这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1144856|function+splitQueryText($query)+{
++++//+the+regex+needs+a+trailing+semicolon
++++$query+=+trim($query);

++++if+(substr($query,+-1)+!=+";")
++++++++$query+.=+";";

++++//+i+spent+3+days+figuring+out+this+line
++++preg_match_all("/(?>[%5E;']%7C(''%7C(?>'([%5E']%7C\\')*[%5E\\\]')))%2B;/ixU",+$query,+$matches,+PREG_SET_ORDER);

++++$querySplit+=+"";

++++foreach+($matches+as+$match)+{
++++++++//+get+rid+of+the+trailing+semicolon
++++++++$querySplit[]+=+substr($match[0],+0,+-1);
++++}

++++return+$querySplit;
}

$queryList+=+splitQueryText($inputText);

foreach+($queryList+as+$query)+{
++++$result+=+mysql_query($query);
}|code-block|syntax|javascript|1144857|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I ran into the same problem. I solved it using a regular expression:

<pre><code>function splitQueryText($query) {
 // the regex needs a trailing semicolon
 $query = trim($query);

 if (substr($query, -1) != ";")
 $query .= ";";

 // i spent 3 days figuring out this line
 preg_match_all("/(?&gt;[^;']|(''|(?&gt;'([^']|\\')*[^\\\]')))+;/ixU", $query, $matches, PREG_SET_ORDER);

 $querySplit = "";

 foreach ($matches as $match) {
 // get rid of the trailing semicolon
 $querySplit[] = substr($match[0], 0, -1);
 }

 return $querySplit;
}

$queryList = splitQueryText($inputText);

foreach ($queryList as $query) {
 $result = mysql_query($query);
}
</code></pre>

blocks|key|1229913|text|您可以使用phpMyAdmin导入文件。即使它是巨大的，只要使用UploadDir配置目录，上传到那里并从phpMyAdmin导入页面中选择它。一旦文件处理接近PHP限度，phpMyAdmin中断导入，再次显示带有预定义值的导入页面，指示在导入中应继续的位置。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1229914|entityMap|0|LINK|mutability|MUTABLE|url|http://wiki.phpmyadmin.net/pma/Config#UploadDir^0|W|9|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can use phpMyAdmin for importing the file. Even if it is huge, just use <a href="http://wiki.phpmyadmin.net/pma/Config#UploadDir" rel="nofollow noreferrer">UploadDir</a> configuration directory, upload it there and choose it from phpMyAdmin import page. Once file processing will be close to the PHP limits, phpMyAdmin interrupts importing, shows you again import page with predefined values indicating where to continue in the import.

blocks|key|1229965|text|导出|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1229966|第一步是以合理的格式获取输入，以便在导出时进行解析。从您的问题中可以看出，您可以控制此数据的导出，而不是导入。|1229967|~:+mysqldump+test+--opt+--skip-extended-insert+%7C+grep+-v+'%5E--'+%7C+grep+.+>+test.sql|code-block|syntax|javascript|1229968|这将排除所有注释行和空行的测试数据库转储到test.sql中。它还禁用扩展插入，这意味着每行有一个INSERT语句。这将有助于限制导入期间的内存使用，但代价是导入速度。|1229969|导入|1229970|导入脚本非常简单：|1229971|<?php

$mysqli+=+new+mysqli('localhost',+'hobodave',+'p4ssw3rd',+'test');
$handle+=+fopen('test.sql',+'rb');
if+($handle)+{
++++while+(!feof($handle))+{
++++++++//+This+assumes+you+don't+have+a+row+that+is+>+1MB+(1000000)
++++++++//+which+is+unlikely+given+the+size+of+your+DB
++++++++//+Note+that+it+has+a+DIRECT+effect+on+your+scripts+memory
++++++++//+usage.
++++++++$buffer+=+stream_get_line($handle,+1000000,+";\n");
++++++++$mysqli->query($buffer);
++++}
}
echo+"Peak+MB:+",memory_get_peak_usage(true)/1024/1024;|1229972|这将利用低得离谱的内存，如下所示：|1229973|daves-macbookpro:~+hobodave$+du+-hs+test.sql+
+15M++++test.sql
daves-macbookpro:~+hobodave$+time+php+import.php+
Peak+MB:+1.75
real++++2m55.619s
user++++0m4.998s
sys+0m4.588s|1229974|也就是说，您在3分钟内处理了一个15+MB的mysqldump，最大内存使用量为1.75MB。|1229975|替代出口|1229976|如果您有足够高的memory_limit，而且速度太慢，可以使用以下导出进行尝试：|1229977|~:+mysqldump+test+--opt+%7C+grep+-v+'%5E--'+%7C+grep+.+>+test.sql|1229978|这将允许扩展插入，在单个查询中插入多行。以下是同一数据库的统计数据：|1229979|daves-macbookpro:~+hobodave$+du+-hs+test.sql+
+11M++++test.sql
daves-macbookpro:~+hobodave$+time+php+import.php+
Peak+MB:+3.75
real++++0m23.878s
user++++0m0.110s
sys+0m0.101s|1229980|请注意，它以3.75MB的速度使用了2倍以上的RAM，但花费的时间约为1/6。我建议尝试这两种方法，看看哪种方法适合你的需要。|1229981|编辑：|1229982|使用CHAR、VARCHAR、二进制、VARBINARY和BLOB字段类型，我无法在任何mysqldump输出中真正显示换行符。如果您有BLOB/二进制字段，那么请使用以下内容，以防万一：|1229983|~:+mysqldump5+test+--hex-blob+--opt+%7C+grep+-v+'%5E--'+%7C+grep+.+>+test.sql|1229984|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1I|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1J|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1K|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1L|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1M|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1N|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|1O|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1R|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|1S|8|@]|9|@]|A|$G|H]]|$1|12|3|13|5|6|7|1T|8|@]|9|@]|A|$]]|$1|14|3|15|5|F|7|1U|8|@]|9|@]|A|$G|H]]|$1|16|3|17|5|6|7|1V|8|@]|9|@]|A|$]]|$1|18|3|19|5|6|7|1W|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|1X|8|@]|9|@]|A|$]]|$1|1C|3|1D|5|F|7|1Y|8|@]|9|@]|A|$G|H]]|$1|1E|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]]|1F|$]]

<h2>Export</h2>

The first step is getting the input in a sane format for parsing when you export it. From your question
it appears that you have control over the exporting of this data, but not the importing.

<pre><code>~: mysqldump test --opt --skip-extended-insert | grep -v '^--' | grep . &gt; test.sql
</code></pre>

This dumps the test database excluding all comment lines and blank lines into test.sql. It also disables 
extended inserts, meaning there is one INSERT statement per line. This will help limit the memory usage
during the import, but at a cost of import speed.

<h2>Import</h2>

The import script is as simple as this:

<pre><code>&lt;?php

$mysqli = new mysqli('localhost', 'hobodave', 'p4ssw3rd', 'test');
$handle = fopen('test.sql', 'rb');
if ($handle) {
 while (!feof($handle)) {
 // This assumes you don't have a row that is &gt; 1MB (1000000)
 // which is unlikely given the size of your DB
 // Note that it has a DIRECT effect on your scripts memory
 // usage.
 $buffer = stream_get_line($handle, 1000000, ";\n");
 $mysqli-&gt;query($buffer);
 }
}
echo "Peak MB: ",memory_get_peak_usage(true)/1024/1024;
</code></pre>

This will utilize an absurdly low amount of memory as shown below:

<pre><code>daves-macbookpro:~ hobodave$ du -hs test.sql 
 15M test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 1.75
real 2m55.619s
user 0m4.998s
sys 0m4.588s
</code></pre>

What that says is you processed a 15MB mysqldump with a peak RAM usage of 1.75 MB in just under 3 minutes.

<h2>Alternate Export</h2>

If you have a high enough memory_limit and this is too slow, you can try this using the following export:

<pre><code>~: mysqldump test --opt | grep -v '^--' | grep . &gt; test.sql
</code></pre>

This will allow extended inserts, which insert multiple rows in a single query. Here are the statistics for the same datbase:

<pre><code>daves-macbookpro:~ hobodave$ du -hs test.sql 
 11M test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 3.75
real 0m23.878s
user 0m0.110s
sys 0m0.101s
</code></pre>

Notice that it uses over 2x the RAM at 3.75 MB, but takes about 1/6th as long. I suggest trying both methods and seeing which suits your needs.

<h3>Edit:</h3>

I was unable to get a newline to appear literally in any mysqldump output using any of CHAR, VARCHAR, BINARY, VARBINARY, and BLOB field types. If you do have BLOB/BINARY fields though then please use the following just in case:

<pre><code>~: mysqldump5 test --hex-blob --opt | grep -v '^--' | grep . &gt; test.sql
</code></pre>

blocks|key|1144889|text|如果不进行解析，就无法可靠地拆分查询。下面是使用正则表达式不可能正确拆分的有效SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1144890|SELECT+";";+SELECT+";\";+a;";
SELECT+";
++++abc";|code-block|syntax|javascript|1144891|我用PHP编写了一个小的SqlFormatter类，其中包括一个查询令牌程序。我向它添加了一个splitQuery方法，可以可靠地拆分所有查询(包括上面的示例)。|1144892|https://github.com/jdorn/sql-formatter/blob/master/SqlFormatter.php|offset|length|1144893|如果不需要，可以移除格式并突出显示方法。|1144894|缺点之一是它要求整个sql字符串都在内存中，如果您正在处理大型sql文件，这可能是一个问题。我确信，只要稍加修改，您就可以让getNextToken方法在文件指针上工作。|1144895|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|1V|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|10|8|@]|9|@$K|11|L|12|1|13]]|A|$]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|15|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|J]]]]

Splitting a query cannot be reliably done without parsing. Here is valid SQL that would be impossible to split correctly with a regular expression.

<pre><code>SELECT ";"; SELECT ";\"; a;";
SELECT ";
 abc";
</code></pre>

I wrote a small SqlFormatter class in PHP that includes a query tokenizer. I added a splitQuery method to it that splits all queries (including the above example) reliably.

<a href="https://github.com/jdorn/sql-formatter/blob/master/SqlFormatter.php" rel="nofollow">https://github.com/jdorn/sql-formatter/blob/master/SqlFormatter.php</a>

You can remove the format and highlight methods if you don't need them.

One downside is that it requires the whole sql string to be in memory, which could be a problem if you're working with huge sql files. I'm sure with a little bit of tinkering, you could make the getNextToken method work on a file pointer instead.

blocks|key|1230044|text|首先，非常感谢这个话题。这为我节省了很多时间:)，让我为您的代码做一些小的修正。有时，如果触发器或过程位于转储文件中，则仅检查；分隔符是不够的。在这种情况下，sql代码中的某些内容可能是分隔符，可以说语句将不以；而是以某些内容结尾。例如，xxx.sql中的一个部分：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1230045|++++DELIMITER+//
++++CREATE+TRIGGER+`mytrigger`+BEFORE+INSERT+ON+`mytable`
++++FOR+EACH+ROW+BEGIN
+++++++++SET+NEW.`create_time`+=+NOW();
++++END
++++//
++++DELIMITER+;|code-block|syntax|javascript|1230046|因此，首先需要有一个错误，以检测该查询没有结束；并删除未限定的查询块，因为mysql_query不需要分隔符(分隔符是字符串的结尾)，因此mysql_query需要如下内容：|1230047|++++CREATE+TRIGGER+`mytrigger`+BEFORE+INSERT+ON+`mytable`
++++FOR+EACH+ROW+BEGIN
+++++++++SET+NEW.`create_time`+=+NOW();
++++END;|1230048|这是一个固定的代码：|1230049|++++function+SplitSQL($file,+$delimiter+=+';')
++++{
++++++++set_time_limit(0);++++++++++++
++++++++$matches+=+array();
++++++++$otherDelimiter+=+false;
++++++++if+(is_file($file)+===+true)+{
++++++++++++$file+=+fopen($file,+'r');
++++++++++++if+(is_resource($file)+===+true)+{
++++++++++++++++$query+=+array();
++++++++++++++++while+(feof($file)+===+false)+{
++++++++++++++++++++$query[]+=+fgets($file);
++++++++++++++++++++if+(preg_match('~'+.+preg_quote('delimiter',+'~')+.+'\s*([%5E\s]%2B)$~iS',+end($query),+$matches)+===+1){+++++
++++++++++++++++++++++++//DELIMITER+DIRECTIVE+DETECTED
++++++++++++++++++++++++array_pop($query);+//WE+DON'T+NEED+THIS+LINE+IN+SQL+QUERY
++++++++++++++++++++++++if(+$otherDelimiter+=+(+$matches[1]+!=+$delimiter+)){
++++++++++++++++++++++++}else{
++++++++++++++++++++++++++++//THIS+IS+THE+DEFAULT+DELIMITER,+DELETE+THE+LINE+BEFORE+THE+LAST+(THAT+SHOULD+BE+THE+NOT+DEFAULT+DELIMITER)+AND+WE+SHOULD+CLOSE+THE+STATEMENT++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++++array_pop($query);
++++++++++++++++++++++++++++$query[]=$delimiter;
++++++++++++++++++++++++}++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++}++++++++++++++++++++++++
++++++++++++++++++++if+(+!$otherDelimiter+&&+preg_match('~'+.+preg_quote($delimiter,+'~')+.+'\s*$~iS',+end($query))+===+1)+{++++++++++++++++++++++++++++
++++++++++++++++++++++++$query+=+trim(implode('',+$query));
++++++++++++++++++++++++if+(mysql_query($query)+===+false){
++++++++++++++++++++++++++++echo+'<h3>ERROR:+'+.+$query+.+'</h3>'+.+"\n";
++++++++++++++++++++++++}else{
++++++++++++++++++++++++++++echo+'<h3>SUCCESS:+'+.+$query+.+'</h3>'+.+"\n";
++++++++++++++++++++++++}
++++++++++++++++++++++++while+(ob_get_level()+>+0){
++++++++++++++++++++++++++++ob_end_flush();
++++++++++++++++++++++++}
++++++++++++++++++++++++flush();++++++++++++++++++++++++
++++++++++++++++++++}
++++++++++++++++++++if+(is_string($query)+===+true)+{
++++++++++++++++++++++++$query+=+array();
++++++++++++++++++++}
++++++++++++++++}++++++++++++++++++++
++++++++++++++++return+fclose($file);
++++++++++++}
++++++++}
++++++++return+false;
}|1230050|我希望我也能帮助别人。祝您今天愉快!|1230051|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

First at all thanks for this topic. This saved a lot of time for me :)
And let me to make little fix for your code.
Sometimes if TRIGGERS or PROCEDURES is in dump file, it is not enough to examine the ; delimiters.
In this case may be DELIMITER [something] in sql code, to say that the statement will not end with ; but [something]. For example a section in xxx.sql:

<pre><code> DELIMITER //
 CREATE TRIGGER `mytrigger` BEFORE INSERT ON `mytable`
 FOR EACH ROW BEGIN
 SET NEW.`create_time` = NOW();
 END
 //
 DELIMITER ;
</code></pre>

So first need to have a falg, to detect, that query does not ends with ;
And delete the unqanted query chunks, because the mysql_query does not need delimiter 
(the delimiter is the end of string) 
so mysql_query need someting like this:

<pre><code> CREATE TRIGGER `mytrigger` BEFORE INSERT ON `mytable`
 FOR EACH ROW BEGIN
 SET NEW.`create_time` = NOW();
 END;
</code></pre>

So a little work and here is the fixed code:

<pre><code> function SplitSQL($file, $delimiter = ';')
 {
 set_time_limit(0); 
 $matches = array();
 $otherDelimiter = false;
 if (is_file($file) === true) {
 $file = fopen($file, 'r');
 if (is_resource($file) === true) {
 $query = array();
 while (feof($file) === false) {
 $query[] = fgets($file);
 if (preg_match('~' . preg_quote('delimiter', '~') . '\s*([^\s]+)$~iS', end($query), $matches) === 1){ 
 //DELIMITER DIRECTIVE DETECTED
 array_pop($query); //WE DON'T NEED THIS LINE IN SQL QUERY
 if( $otherDelimiter = ( $matches[1] != $delimiter )){
 }else{
 //THIS IS THE DEFAULT DELIMITER, DELETE THE LINE BEFORE THE LAST (THAT SHOULD BE THE NOT DEFAULT DELIMITER) AND WE SHOULD CLOSE THE STATEMENT 
 array_pop($query);
 $query[]=$delimiter;
 } 
 } 
 if ( !$otherDelimiter &amp;&amp; preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1) { 
 $query = trim(implode('', $query));
 if (mysql_query($query) === false){
 echo '&lt;h3&gt;ERROR: ' . $query . '&lt;/h3&gt;' . "\n";
 }else{
 echo '&lt;h3&gt;SUCCESS: ' . $query . '&lt;/h3&gt;' . "\n";
 }
 while (ob_get_level() &gt; 0){
 ob_end_flush();
 }
 flush(); 
 }
 if (is_string($query) === true) {
 $query = array();
 }
 } 
 return fclose($file);
 }
 }
 return false;
}
</code></pre>

I hope i could help somebody too.
Have a nice day!

blocks|key|1144918|text|http://www.ozerov.de/bigdump/在导入200%2B+MB文件方面非常有用。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1144919|注意:+SQL文件应该已经存在于服务器中，这样就可以在没有任何问题的情况下完成该过程。|1144920|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ozerov.de/bigdump/^0|0|T|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

<a href="http://www.ozerov.de/bigdump/" rel="nofollow">http://www.ozerov.de/bigdump/</a> was very useful for me in importing 200+ MB sql file.

Note:
SQL file should be already present in the server so that the process can be completed without any issue

I have a situation where I have to update a web site on a shared hosting provider. The site has a CMS. Uploading the CMS's files is pretty straightforward using FTP.

I also have to import a big (relative to the confines of a PHP script) database file (Around 2-3 MB uncompressed). Mysql is closed for access from the outside, so I have to upload a file using FTP, and start a PHP script to import it. Sadly, I do not have access to the <code>mysql</code> command line function so I have to parse and query it using native PHP. I also can't use LOAD DATA INFILE. I also can't use any kind of interactive front-end like phpMyAdmin, it needs to run in an automated fashion. I also can't use <code>mysqli_multi_query()</code>.

Does anybody know or have a already coded, simple solution that reliably splits such a file into single queries (there could be multi-line statements) and runs the query. I would like to avoid to start fiddling with it myself due to the many gotchas that I'm likely to come across (How to detect whether a field delimiter is part of the data; how to deal with line breaks in memo fields; and so on). There must be a ready made solution for this.

Best practice: Import mySQL file in PHP; split queries

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个情况，我必须更新一个网站上的共享主机提供商。该网站有一个CMS。使用FTP上传CMS的文件非常简单。我还必须导入一个大的(相对于PHP脚本的限制)数据库文件(大约2-3 MB未压缩)。Mysql是关闭的，以便从外部访问，所以我必须使用FTP上传一个文件，然后启动一个PHP脚本来导入它。遗憾的是，我无法访问mys...

问最佳实践:在PHP中导入mySQL文件；拆分查询
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最佳实践:在PHP中导入mySQL文件；拆分查询EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问最佳实践:在PHP中导入mySQL文件；拆分查询
EN