麻烦PHP的MySQLI标志MYSQL_PRI_KEY_FLAG [英] Trouble with PHP's MySQLI flag MYSQL_PRI_KEY_FLAG
问题描述
我遇到了一个奇怪的行为，想知道我是在做错误还是API确实是这样做的。
I came across a strange behaviour and want to know if I am doing a misstake or the API is indeed working this way.
我说的是MYSQLI，我的自己的PHP frameowrk可以分析表来构建一个动态的实体类，它到目前为止工作得非常好，但是我很难与MYSQLI_PRI_KEY_FLAG斗争，它应该告诉我列的索引是否是PRIMARY_KEY索引的一部分。
I am talking about MYSQLI, my own PHP frameowrk can analyze the table to build a dynamic Entity class out of it which works very well so far, however im struggling with the MYSQLI_PRI_KEY_FLAG which should tell me if the column's index is part of a PRIMARY_KEY index.
但是给出的是下表：
ip_ranges
cols is：
account_id, min, max, comment
此表中唯一的索引是一个名称为的唯一索引：
The only index on this table is a unique index with name:
account_id_unique
超过3列：
account_id, min, max
肯定没有定义PRIMARY_KEY。
There is definetly no PRIMARY_KEY defined.
到目前为止一切顺利，你需要的最后一个信息是array $ this-> columns包含用以下内容获取的所有列数据：
So far so good, the last info you need is that the array $this->columns contains all columns data fetched with:
Mysqli_Result::fetch_fields
现在我使用以下2个成员函数来确定列是否具有PRIMARY_KEY标志：
Now I use the following 2 member functions to determine if a column has the flag PRIMARY_KEY:
public function columnHasFlag($columnName, $flag) {
$retVal = false;
if(isset($this->columns[strtolower($columnName)]) == true && $this->columns[strtolower($columnName)]["flags"] & $flag) {
$retVal = true;
}
return $retVal;
}
public function determinePrimaryKeyColumn() {
if($this->columnPrimaryKey === null) {
foreach($this->columns as $columnName => $data) {
if($this->columnHasFlag($columnName, MYSQLI_PRI_KEY_FLAG) == true) {
$this->columnPrimaryKey = $columnName;
break;
}
}
}
}
奇怪的是，我得到上面的示例表，当我在$ this-> columns [strtolower（$ columnName）] [flags]上使用'vardump'时，它包含account_id列的标志，它返回给我值：int（49155）
The strange this is that I get for the example table above, when I use 'vardump' on $this->columns[strtolower($columnName)]["flags"] which contains the flags for the column "account_id" it returns me the value: "int(49155)"
和columnHasFlag为语句返回true：
$ this-> columns [strtolower（$ columnName）] [ 旗帜]& $ flag
and columnHasFlag returns me "true" for statement: $this->columns[strtolower($columnName)]["flags"] & $flag
（$ flag计算常量MYSQLI_PRI_KEY_VALUE，其值为2。
($flag evaluates the constant MYSQLI_PRI_KEY_VALUE which evaluates to "2".
那么标志是如何设置的呢？我认为这个标志只设置为PRIMARY键索引，其必须具有名称primary？或者它是否设置为任何其他UNIQUE_KEY？
So how it is possible that the flag is set? I thought this flag is only set for the PRIMARY key index which must have the name primary? Or is it set for any other UNIQUE_KEY aswell?
但是，这看起来很奇怪，因为在mysqli_result :: fetch_field文档中有UNIQUE和PRIMARY键的单独标志：
However, this seems rather strange because there are seperate flags for UNIQUE and the PRIMARY key in the mysqli_result::fetch_field documentation:
NOT_NULL_FLAG = 1
PRI_KEY_FLAG = 2
UNIQUE_KEY_FLAG = 4
...
(http://php.net/manual/en/mysqli-result.fetch-field.php)
所以我的问题是，这是一个bug还是我在某种程度上比较了错误的标志吗？
或者我在文档中遗漏了什么？
So my question, is this a bug or am I comparing the flags wrong somehow? Or did I miss something in the documentation?
推荐答案
我不知道认为这是一个错误，这里引自 MySQL Referen ce手册
I don't think it is a bug, here is a quote from MySQL Reference Manual
如果UNIQUE索引不能包含NULL
值，则它可能显示为PRI表中的主键。如果多个列形成复合UNIQUE索引，则UNIQUE索引可以
显示为MUL;
虽然列的组合是唯一的，但每列
仍然可以保留给定值的多次出现。
A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value.
这是我用来测试该行为的代码。
This is the code that I used to test that behavior.
$flags = array( 'NOT_NULL_FLAG' => MYSQLI_NOT_NULL_FLAG,
'PRI_KEY_FLAG' => MYSQLI_PRI_KEY_FLAG,
'UNIQUE_KEY_FLAG' => MYSQLI_UNIQUE_KEY_FLAG,
'MULTIPLE_KEY_FLAG' => MYSQLI_MULTIPLE_KEY_FLAG,
'BLOB_FLAG' => MYSQLI_BLOB_FLAG,
'UNSIGNED_FLAG' => MYSQLI_UNSIGNED_FLAG,
'ZEROFILL_FLAG' => MYSQLI_ZEROFILL_FLAG,
'BINARY_FLAG' => MYSQLI_BINARY_FLAG,
'ENUM_FLAG' => MYSQLI_ENUM_FLAG,
'AUTO_INCREMENT_FLAG' => MYSQLI_AUTO_INCREMENT_FLAG,
'TIMESTAMP_FLAG' => MYSQLI_TIMESTAMP_FLAG,
'SET_FLAG' => MYSQLI_SET_FLAG,
'PART_KEY_FLAG' => MYSQLI_PART_KEY_FLAG,
'NUM_FLAG' => MYSQLI_NUM_FLAG );
function info_array( $result ) {
return array_reduce( $result->fetch_fields(), function( $result, $info ) {
$result[ $info->name ] = $info;
return $result;
} );
}
function get_flags( $meta, $flags ) {
return array_filter( $flags, function( $flag ) use ( $meta ) {
return $meta->flags & $flag;
} );
}
$result = $mysqli->query( 'SELECT * FROM ip_ranges LIMIT 1' );
$column_flags = array();
foreach( info_array( $result ) as $name => $value ) {
$column_flags[ $name ] = get_flags( $value, $flags );
}
print_r( $column_flags );
所以我在DB中创建了一个表，其中包含3列以上的唯一索引，得到的输出就像你说的那样，所有3列都设置了主键标志。
So I created a table in DB with unique index over 3 columns an got an output like you said, all 3 columns have primary key flag set.
Array (
[account_id] => Array (
[NOT_NULL_FLAG] => 1
[PRI_KEY_FLAG] => 2
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768 )
[min] => Array (
[NOT_NULL_FLAG] => 1
[PRI_KEY_FLAG] => 2
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768)
[max] => Array (
[NOT_NULL_FLAG] => 1
[PRI_KEY_FLAG] => 2
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768 )
[comment] => Array (
[NOT_NULL_FLAG] => 1 )
)
显示列查询（<$ c来自mysql admin的$ c> SHOW COLUMNS FROM ip_ranges ）得到了相同的结果。
A show columns query ( SHOW COLUMNS FROM ip_ranges ) in mysql admin got me the same result.
Field Type Null Key Default Extra
account_id int(11) NO PRI NULL
min int(11) NO PRI NULL
max int(11) NO PRI NULL
comment varchar() NO NULL
然后我创建了一个主键，得到了以下输出：
Then I created a primary key, and got the following output:
Array (
[id] => Array (
[NOT_NULL_FLAG] => 1
[PRI_KEY_FLAG] => 2
[AUTO_INCREMENT_FLAG] => 512
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768 )
[account_id] => Array (
[NOT_NULL_FLAG] => 1
[MULTIPLE_KEY_FLAG] => 8
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768
)
[min] => Array(
[NOT_NULL_FLAG] => 1
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768 )
[max] => Array (
[NOT_NULL_FLAG] => 1
[PART_KEY_FLAG] => 16384
[NUM_FLAG] => 32768 )
[comment] => Array (
[NOT_NULL_FLAG] => 1 )
)
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
account_id int(11) NO MUL NULL
min int(11) NO NULL
max int(11) NO NULL
comment varchar() NO NULL
所以最后， php和mysql报告的内容相同。
So in the end, the php and mysql are reporting the same thing.
这篇关于麻烦PHP的MySQLI标志MYSQL_PRI_KEY_FLAG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
