When I am doing a simple insert via the PHP mysqli API with prepared statements on Windows within a PHP process, the defined AUTO_INCREMENT column is increased by 2 instead of 1:
当我在PHP过程中通过PHP mysqli API在Windows上使用预准备语句进行简单插入时,定义的AUTO_INCREMENT列增加2而不是1:
INSERT INTO `table` (`name`) VALUES (?)
It get increased by 1 when doing multiple inserts (one by one in separate transactions) within one PHP process.
在一个PHP进程中进行多次插入(在单独的事务中逐个进行)时,它会增加1。
It always get increased by 1 when I use the same SQL query via phpmyadmin.
当我通过phpmyadmin使用相同的SQL查询时,它总是增加1。
There are no other INSERT or UPDATE statements before or after the mentioned INSERT. Only a SHOW and some SELECT statements before.
在提到的INSERT之前或之后没有其他INSERT或UPDATE语句。之前只有一个SHOW和一些SELECT语句。
I cannot find the cause for this problem. What can be the causes for such a behaviour?
我找不到这个问题的原因。造成这种行为的原因是什么?
Here the main code parts:
这里主要代码部分:
<?php
class DB
{
private function __construct($host, $username, $password, $schema, $port, $socket)
{
if(is_null(self::$DB))
{
self::$DB = new \MySQLi((string) $host, (string) $username, (string) $password, (string) $schema, (int) $port, (string) $socket);
self::$DB->set_charset('utf8');
}
}
// [...]
public function __destruct()
{
if(!is_null(self::$DB))
self::$DB->close();
}
// [...]
public static function connect($host = '', $username = '', $password = '', $schema = '', $port = 0, $socket = '')
{
if(is_null(self::$instance))
{
$MD = new \MySQLi_driver();
$MD->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;
// [...]
self::$instance = new self($host, $username, $password, $schema, $port, $socket);
}
return self::$instance;
}
// [...]
public static function __callStatic($name, $args)
{
self::connect();
switch(true)
{
case in_array($name, array('insert', 'select', 'update', 'delete', 'show', 'describe', 'explain')):
$query = isset($args[0]) ? (string) $args[0] : '';
$vals = isset($args[1]) ? $args[1] : array();
$select = isset($args[2]) ? trim((string) $args[2]) : 'array';
$empty = isset($args[3]) ? $args[3] : array();
$types = isset($args[4]) ? trim((string) $args[4]) : '';
return self::dml(in_array($name, array('show', 'describe', 'explain')) ? 'select' : $name, $query, $vals, $select, $empty, $types);
break;
}
//[...]
}
// [...]
public static function dml($type, $query, $vals = array(), $select = 'array', $empty = array(), $types = '')
{
// [...]
if(!empty($vals) || mb_strpos($query,'?') !== false)
{
if(!$stmt = self::$DB->prepare($query))
throw new DBException('Failed to prepare statement '.htmlspecialchars($query).PHP_EOL.self::$DB->error.' ('.self::$DB->sqlstate.').');
$args = array();
if(empty($types))
{
foreach($vals as &$val)
{
$t = gettype($val);
if($t == 'string' || $t == 'NULL')
$types.= 's';
elseif($t == 'integer' || $t == 'boolean')
$types.= 'i';
elseif($t == 'double' || $t == 'float')
$types.= 'd';
else
throw new DBException('Its not possible to automatically assign a value of type '.$t.'. Please specify the corresponding types manually.');
$args[] = $val;
}
}
else
{
foreach($vals as &$val)
$args[] = $val;
}
array_unshift($args, $types);
$RC = new \ReflectionClass($stmt);
$RM = $RC->getMethod('bind_param');
if(!$RM->invokeArgs($stmt, $args))
throw new DBException('Failed to bind params.'.PHP_EOL.self::$DB->error.' ('.self::$DB->sqlstate.').');
if(!$stmt->execute())
throw new DBException('Failed to execute Statement.'.PHP_EOL.self::$DB->error.' ('.self::$DB->sqlstate.').');
if($type == 'select')
{
// [...]
}
else
{
$return = $type == 'insert' && self::$DB->insert_id > 0 ? self::$DB->insert_id : self::$DB->affected_rows;
$stmt->close();
return $return;
}
}
else
{
// [...]
}
}
}
?>
AND:
echo DB::insert("INSERT INTO `table` (`name`) VALUES (?)", ["test"]);
The Query-Log shows 2 inserts. This causes the additional increment. But, when i put an echo into the corresponding DB::dml()-method, its only outputted once, thus called once. The mysql-query-log:
Query-Log显示2个插入。这会导致额外的增量。但是,当我将回声放入相应的DB :: dml() - 方法时,它只输出一次,因此调用一次。 mysql-query-log:
151026 12:54:49 3 Connect XXX@localhost on XXX
3 Query SET NAMES utf8
3 Query SELECT DATABASE() AS `schema`
3 Query SHOW GRANTS FOR CURRENT_USER
3 Prepare INSERT INTO `table` (`name`) VALUES (?)
3 Execute INSERT INTO `table` (`name`) VALUES ('testinsert22')
3 Close stmt
3 Quit
4 Connect XXX@localhost on XXX
4 Query SET NAMES utf8
4 Query SELECT DATABASE() AS `schema`
4 Query SHOW GRANTS FOR CURRENT_USER
4 Prepare INSERT INTO `table` (`name`) VALUES (?)
4 Execute INSERT INTO `table` (`name`) VALUES ('testinsert22')
4 Close stmt
4 Quit
1 个解决方案
#1
0
Ok. I finally got it. The problem is: I redirect all requests to my index.php and do all the rest with PHP. I tested some classes behaviour with simple outputs. One of the tests lead to the problem above. I checked the apache access log and noticed repeated favicon.ico requests. These requests - automatically produced by major browsers (see also How to prevent favicon.ico requests?) - also got to the index.php - and looks like some redirect to the browser? normally this is all handled in my application framework, but i disabled it while i tested.
好。我终于明白了。问题是:我将所有请求重定向到我的index.php,并使用PHP完成所有其他请求。我用简单的输出测试了一些类行为。其中一项测试导致了上述问题。我检查了apache访问日志,发现重复的favicon.ico请求。这些请求 - 主要浏览器自动生成(另请参见如何防止favicon.ico请求?) - 也得到了index.php - 看起来有些重定向到浏览器?通常这都是在我的应用程序框架中处理的,但我在测试时禁用了它。
#1
0
Ok. I finally got it. The problem is: I redirect all requests to my index.php and do all the rest with PHP. I tested some classes behaviour with simple outputs. One of the tests lead to the problem above. I checked the apache access log and noticed repeated favicon.ico requests. These requests - automatically produced by major browsers (see also How to prevent favicon.ico requests?) - also got to the index.php - and looks like some redirect to the browser? normally this is all handled in my application framework, but i disabled it while i tested.
好。我终于明白了。问题是:我将所有请求重定向到我的index.php,并使用PHP完成所有其他请求。我用简单的输出测试了一些类行为。其中一项测试导致了上述问题。我检查了apache访问日志,发现重复的favicon.ico请求。这些请求 - 主要浏览器自动生成(另请参见如何防止favicon.ico请求?) - 也得到了index.php - 看起来有些重定向到浏览器?通常这都是在我的应用程序框架中处理的,但我在测试时禁用了它。