php+mysql:在mysql中插入一个php数组

2022-01-09 00:00:00 arrays insert php mysql

我有一个包含 30000 多个条目的数组，需要进入 MySQL 表.

I have an array with 30000 plus entries that need to go into a MySQL table.

最佳做法是什么?从这里?假设数据库中的 [0]、[1] 和 [2] 将是标题"、类型"和客户"

What is the best practice? From here? Lets say [0], [1] and [2] in the database would be 'title', 'type' and 'customer'

是否添加与表中的列名匹配的键名并调用som魔术"函数?或者手动构建查询...

Is it add key names matching the column names in the table and call som "magic" function? Or build the query manually...

Array
(
    [0] => Array
        (
            [0] => 2140395946
            [1] => 1SAP
            [2] => 0041451463
        )

    [1] => Array
        (
            [0] => 2140411607
            [1] => 2SAP
            [2] => 0041411940
        )

    [2] => Array
        (
            [0] => 2140706194
            [1] => 4SAP
            [2] => 0041411943
        )
etc. etc.

更新 - 基于答案

感谢您的回答.

解决方案通常是手动创建 SQL 字符串，并且所有行都可以作为一个插入:

The solution would normally be to manually create the SQL-string and all rows can be inserted as one:

INSERT INTO `tx_opengate_table` (`machine` ,`customer` ,`type`)
VALUES
  ('m123', 'dfkj45', 'A'),
  ('m137', 'kfkj49', 'A'), "repeat this line for each entry in the array"
  ... ... ...
  ('m654321', '34dgf456', 'C4') "end with out comma, or remove last comma"
;

TYPO3专用

我碰巧在 CMS TYPO3 中执行此操作，刚刚发现不久前添加的一个新功能:

I happen to do this in the CMS TYPO3 and just came across a new function added not that long ago:

//Insert new rows
$table = 'tx_opengate_stuff';
$fields = array('machine','type','customer');
$lines = "array as given above"
$GLOBALS['TYPO3_DB']->exec_INSERTmultipleRows($table,$fields,$lines);

推荐答案

我会说自己构建它.你可以这样设置:

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
}
$query = rtrim($query,",");//remove the extra comma
//execute query

如果有必要，不要忘记转义引号.

Don't forget to escape quotes if it's necessary.

另外，请注意不要一次发送太多数据.您可能必须分块执行它，而不是一次全部执行.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

相关文章