詳解MySQL中的NULL值

詳解MySQL中的NULL值。本站提示廣大學習愛好者：（詳解MySQL中的NULL值）文章只能為提供參考，不一定能成為您想要的結果。以下是詳解MySQL中的NULL值正文

我們曾經看到應用WHERE子句的SQL SELECT敕令來從MySQL表獲得數據。然則，當我們試圖給的前提比擬字段或列的值為NULL，它不克不及正常任務。

為了處置這類情形，MySQL供給了三年夜運算符

1.     IS NULL: 此運算符前往true，當列的值是NULL。
2.     IS NOT NULL: 運算符前往true，當列的值不是NULL。
3.     <=> 操作符比擬值（分歧於=運算符）為ture，即便兩個NULL值

觸及NULL前提是特別的。不克不及應用 =NULL 或 !=NULL 尋覓NULL值的列。這類比擬老是告知他們能否是真實的掉敗，由於這是弗成能的。即便是NULL=NULL掉敗。

假如要查找是或不是NULL的列，請應用IS NULL或IS NOT NULL。
在敕令提醒符下應用NULL值：

假定一個表tcount_tbl，它包括了兩個的列stutorial_author和tutorial_count，個中一個tutorial_count為NULL 表現的值是未知的
例子:

嘗嘗上面的例子：

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tcount_tbl
  -> (
  -> tutorial_author varchar(40) NOT NULL,
  -> tutorial_count INT
  -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tcount_tbl
  -> (tutorial_author, tutorial_count) values ('mahran', 20);
mysql> INSERT INTO tcount_tbl
  -> (tutorial_author, tutorial_count) values ('mahnaz', NULL);
mysql> INSERT INTO tcount_tbl
  -> (tutorial_author, tutorial_count) values ('Jen', NULL);
mysql> INSERT INTO tcount_tbl
  -> (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran     |       20 |
| mahnaz     |      NULL |
| Jen       |      NULL |
| Gill      |       20 |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql>

可以看到=和!=不應用NULL值，以下所示：

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

要找到，個中tutorial_count列是或不是NULL的記載，查詢應當如許寫：

mysql> SELECT * FROM tcount_tbl 
  -> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz     |      NULL |
| Jen       |      NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
  -> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran     |       20 |
| Gill      |       20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

PHP劇本處置NULL值：

可使用IF ... ELSE前提預備的基本上操作NULL值的查詢。
例子:

上面的示例tutorial_count從內部，然後它與可在表中的值停止比擬。

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('Could not connect: ' . mysql_error());
}
if( isset($tutorial_count ))
{
  $sql = 'SELECT tutorial_author, tutorial_count
      FROM tcount_tbl
      WHERE tutorial_count = $tutorial_count';
}
else
{
  $sql = 'SELECT tutorial_author, tutorial_count
      FROM tcount_tbl
      WHERE tutorial_count IS $tutorial_count';
}

mysql_select_db('TUTORIALS');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
 die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
  echo "Author:{$row['tutorial_author']} <br> ".
     "Count: {$row['tutorial_count']} <br> ".
     "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>