mysql 分段 php_php+mysql实现数据分批插入

1 <?php

2 /**

3 * Created by PhpStorm.

4 * User: Soup

5 * Date: 2016/12/29

6 * Time: 21:04

7 */

8

9 $mysqli = new mysqli('localhost','root','123456','test');

10

11

12

13 if ($mysqli->connect_error) {

14 die('Connect Error (' . $mysqli->connect_errno . ') '

15 . $mysqli->connect_error);

16 }

17

18 /*

19 *@param $data array 要插入的数据

20 *@param $each int 每次插入的条数

21 */

22 function batchInsert($data,$each){

23

24 $num = count($data); // 数据总数

25 $step = ceil( $num/$each); // insert执行总次数

26

27

28 $j = 1;

29 $s = $step;

30

31 foreach ($data as $val){ // 目前仅仅是起到循环作用

32 if($j > $step) break;

33 $arr2 = array_slice($data, ($step - $s) * $each, $each); // 每次取100条

34 $sql = "insert into batch VALUES ";

35 foreach($arr2 as $v){

36 $exist = $GLOBALS['mysqli']->query("select * from batch where number = '$v' limit 1 "); // 检查要插入的数据是否已存在

37 $row = $exist->fetch_array(MYSQLI_ASSOC);

38 if(empty($row)){

39 $sql .= "(null,$v,now()),"; // 将sql语句拼接起来

40 }

41 }

42 $sql = rtrim($sql, ',');

43 $result = $GLOBALS['mysqli']->query($sql);

44 echo $sql,'
';

45 46 47 $j++;

48 $s--;

49 }

50 return $result;

51 }

52

53 // 先测试简单的数字插入

54 for ($i = 1;$i <= 505;++$i){

55 $arr[$i] = $i;

56 }

57

58 var_dump(batchInsert($arr,100));