Rumah >pembangunan bahagian belakang >tutorial php >php中如何执行批量插入数据到mysql

php中如何执行批量插入数据到mysql

WBOY
WBOYasal
2016-06-06 20:36:141495semak imbas

<code>$arr = Array
(
    [0] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1427940803
            [pv_total] => 0.00
        )

    [1] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428477888
            [pv_total] => 0.00
        )

    [2] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428475892
            [pv_total] => 0.00
        )

    [3] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428482901
            [pv_total] => 0.00
        )

    [4] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428483116
            [pv_total] => 0.00
        )

    [5] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428483542
            [pv_total] => 20.00
        )

    [6] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428475881
            [pv_total] => 40.00
        )

    [7] => Array
        (
            [u_id] => 5
            [userid] => feng005
            [status] => 3
            [confirm_time] => 1428474458
            [pv_total] => 100.00
        )

    [8] => Array
        (
            [u_id] => 4
            [userid] => feng004
            [status] => 3
            [confirm_time] => 1428475275
            [pv_total] => 100.00
        )

    [9] => Array
        (
            [u_id] => 3
            [userid] => feng002
            [status] => 3
            [confirm_time] => 1428475478
            [pv_total] => 100.00
        )

    [10] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428475622
            [pv_total] => 100.00
        )

    [11] => Array
        (
            [u_id] => 6
            [userid] => feng006
            [status] => 3
            [confirm_time] => 1428479200
            [pv_total] => 100.00
        )

    [12] => Array
        (
            [u_id] => 6
            [userid] => feng006
            [status] => 3
            [confirm_time] => 1428483519
            [pv_total] => 40.00
        )

);
$count = count($res);
     $sql = "insert into users(periods,uid,username,pv) VALUES ";
           foreach($res as $key=>$vo){
                if($key == $count-1){
                    $sql .= "(".$periods.",".$vo['u_id'].",'".$vo['userid']."',".$vo['pv_total'].")";
                }else{
                    $sql .= "(".$periods.",".$vo['u_id'].",'".$vo['userid']."',".$vo['pv_total']."),";
                }
            };
            M()->query($sql);
</code>

这样能行吗?没怎么用过批量操作数据库,请指导。

回复内容:

<code>$arr = Array
(
    [0] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1427940803
            [pv_total] => 0.00
        )

    [1] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428477888
            [pv_total] => 0.00
        )

    [2] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428475892
            [pv_total] => 0.00
        )

    [3] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428482901
            [pv_total] => 0.00
        )

    [4] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428483116
            [pv_total] => 0.00
        )

    [5] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428483542
            [pv_total] => 20.00
        )

    [6] => Array
        (
            [u_id] => 1
            [userid] => feng003
            [status] => 3
            [confirm_time] => 1428475881
            [pv_total] => 40.00
        )

    [7] => Array
        (
            [u_id] => 5
            [userid] => feng005
            [status] => 3
            [confirm_time] => 1428474458
            [pv_total] => 100.00
        )

    [8] => Array
        (
            [u_id] => 4
            [userid] => feng004
            [status] => 3
            [confirm_time] => 1428475275
            [pv_total] => 100.00
        )

    [9] => Array
        (
            [u_id] => 3
            [userid] => feng002
            [status] => 3
            [confirm_time] => 1428475478
            [pv_total] => 100.00
        )

    [10] => Array
        (
            [u_id] => 2
            [userid] => feng001
            [status] => 3
            [confirm_time] => 1428475622
            [pv_total] => 100.00
        )

    [11] => Array
        (
            [u_id] => 6
            [userid] => feng006
            [status] => 3
            [confirm_time] => 1428479200
            [pv_total] => 100.00
        )

    [12] => Array
        (
            [u_id] => 6
            [userid] => feng006
            [status] => 3
            [confirm_time] => 1428483519
            [pv_total] => 40.00
        )

);
$count = count($res);
     $sql = "insert into users(periods,uid,username,pv) VALUES ";
           foreach($res as $key=>$vo){
                if($key == $count-1){
                    $sql .= "(".$periods.",".$vo['u_id'].",'".$vo['userid']."',".$vo['pv_total'].")";
                }else{
                    $sql .= "(".$periods.",".$vo['u_id'].",'".$vo['userid']."',".$vo['pv_total']."),";
                }
            };
            M()->query($sql);
</code>

这样能行吗?没怎么用过批量操作数据库,请指导。

###代码大概像这样吧,手打的,没有测试###

<code>php</code><code>$sql = 'INSERT INTO TABLE (`u_uid`, `userid`,`status`,`confirm_time`,`pv_total`) VALUES';
foreach($arr as $k => $item) {
    $sql .= '(' . $item['u_uid'] . ',' . $item['userid'] . ',' . $item['status'] . ',' . $item['confirm_time'] . ',' . $item['pv_total'] . '),';
}
$sql = rtrim($sql, ',');
</code>

对,就是这样的。

<code>sql</code><code>INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....),(值1, 值2,....),(值1, 值2,....)
</code>

其实编程这玩意儿,你试试看就知道可不可行了。

Kenyataan:
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn