php使用mysql的NULLIF函数存入null值

在开发的过程中发现一个问题，表中有2个字段name（类型varchar）和 age（类型tinyint），想要name字段写入null值，可以直接使用下面的sql语句：

insert into test(name,age) values ('lily',6);
insert into test(name,age) values ('null','null');
insert into test(name,age) values  (null,null)

那如果要批量存入数据呢？

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8";
$user = "root";
$password = "";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$values = [
    ['name' => 'null', 'age' => 5],
    ['name' => 'null', 'age' => 'null'],
];
$sql = "insert into test(name,age) values ";

foreach ($values as $index => $value) {
    $name = $value['name'];
    $age = $value['age'];
    if ($index > 0) $sql .= ",";
    $sql .= "('$name',$age)";
}
$rows = $pdo->exec($sql);
var_dump($rows);
var_dump($pdo->lastInsertId());

可以看出：数值类型age存入mysql的null，而字符串类型的name存入字符串'null'，因为字符串格式的字段一定要用引号括起来

要解决这个问题，就要使用mysql 的NULLIF函数

NULLIF(expression1, expression2)

如果传递的两个表达式相等，则MySQL NULLIF()函数返回NULL，否则，如果两个表达式都不相等，则返回第一个表达式。

修改如下

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8";
$user = "root";
$password = "";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$values = [
    ['name' => 'daisy', 'age' => 7],
    ['name' => 'null', 'age' => 'null'],
];
$sql = "insert into test(name,age) values ";

foreach ($values as $index => $value) {
    $name = $value['name'] == 'null' || $value['name'] == '' ? '' : $value['name'];
    $age = $value['age'];
    if ($index > 0) $sql .= ",";
    $sql .= "(NULLIF('$name',''),$age)";
}
$rows = $pdo->exec($sql);
var_dump($rows);
var_dump($pdo->lastInsertId());

顺便记录下和null相关的mysql函数

IFNULL(expression1, expression2) : expression1为null则返回expression2，否则返回expression1

ISNULL(expression) ： expression为null则返回1，否则返回 0

顺便记录下mysql的自增id主键和联合主键

开发中发现自增id主键在需要需要某些字段唯一的情况下容易造成记录多的情况，可以使用联合主键来避免。例如需要字段1和字段2在表中有唯一记录，这时可创建字段1和字段2的联合主键或者唯一索引配合replace 语句，避免使用自增主键id新增记录带来的多个客户端同时操作页面造成的字段1和字段2不唯一的情况。

自增id主键在批量删除中还是非常好用的