Kepiye database MySQL nangani pitakon nilai non-null? MySQL ora null pilih statement

database MySQLKepiye cara nangani pitakon nilai non-null?MySQL ora null pilih statement

Null nangani ing MySQL

Kita wis ngerti yen MySQL nggunakake perintah SQL SELECT lan klausa WHERE kanggo maca data ing tabel data, nanging nalika kolom kondisi query sing diwenehake NULL, printah kasebut bisa uga ora bisa digunakake kanthi bener.

Kanggo nangani kahanan iki, MySQL nyedhiyakake telung operator utama:

• IS NULL:Operator iki ngasilake bener nalika nilai kolom NULL.
• IS NOT NULL:Operator ngasilake bener nalika nilai kolom ora NULL.
• <=>:  Operator perbandingan (ora kaya operator =) ngasilake bener nalika rong nilai sing dibandhingake yaiku NULL.

Operasi perbandingan kondisi ing NULL khusus.Sampeyan ora bisa nggunakake = NULL utawa ! =NULL nemokake nilai NULL ing kolom.

Ing MySQL, perbandingan nilai NULL karo nilai liyane (malah NULL) mesthi ngasilake palsu, yaiku NULL = NULL ngasilake palsu.

NULL ditangani ing MySQL nggunakake operator IS NULL lan IS NOT NULL.

Gunakake nilai NULL ing command prompt

Ing conto ing ngisor iki, tabel chenweiliang_test_tbl ing database chenweiliang disetel kanggo ngemot rong kolom, chenweiliang_author lan chenweiliang_count, lan nilai NULL disetel kanggo dilebokake ing chenweiliang_count.

Kadadeyan

Coba conto ing ngisor iki:

root @ host ＃mysql -u root -p password; 输入密码：*******
 mysql > 使用chenweiliang ;
数据库改变了mysql > create table chenweiliang_test_tbl 
 - > （
 - > chenweiliang_author varchar （40 ）NOT NULL ， - > chenweiliang_count INT 
 - > ）;
查询OK ，0 行受影响（0.05 秒）mysql >
 
 
INSERT INTO chenweiliang_test_tbl （chenweiliang_author ，chenweiliang_count ）values （' chenweiliang ' ，20 ）;
mysql > INSERT INTO chenweiliang_test_tbl （chenweiliang_author ，chenweiliang_count ）values （' 陈沩亮博客' ，NULL ）;
mysql > INSERT INTO chenweiliang_test_tbl （chenweiliang_author ，chenweiliang_count ）values （ ' Google ' ，NULL ）;
mysql > INSERT INTO chenweiliang_test_tbl （chenweiliang_author ，chenweiliang_count ）values （' FK ' ，20 ）;
 
mysql > SELECT * from chenweiliang_test_tbl ; + --------------- + -------------- + | chenweiliang_author | chenweiliang_count | + --------------- + -------------- + | chenweiliang | 20 | | 陈沩亮博客| NULL | | Google | NULL | | FK | 20 | + --------------- + -------------- +
 4 行中集合（0.01 秒） 

Ing conto ing ngisor iki sampeyan bisa ndeleng = lan! Operator = ora bisa digunakake:

mysql > SELECT * FROM chenweiliang_test_tbl WHERE chenweiliang_count = NULL ;
空集（0.00 秒）mysql > SELECT * FROM chenweiliang_test_tbl WHERE chenweiliang_count ！= NULL ;
空集（0.01 秒）

Kanggo ngerteni apa kolom chenweiliang_test_tbl ing tabel data NULL, sampeyan kudu nggunakakeIS NULL和Ora NULL, conto ing ngisor iki:

mysql > SELECT * FROM chenweiliang_test_tbl WHERE chenweiliang_count IS NULL ; + --------------- + -------------- + | chenweiliang_author | chenweiliang_count | + --------------- + -------------- + | 陈沩亮博客| NULL | | Google | NULL | + --------------- + -------------- +
 2 行中的组（0.01 秒）的MySQL > SELECT * 从chenweiliang_test_tbl WHERE chenweiliang_count IS NOT 空值 
 
 ; + --------------- + -------------- + | chenweiliang_author | chenweiliang_count | + --------------- + -------------- + | chenweiliang | 20 | | FK | 20 | + --------------- + -------------- +
 2 行中的组（0.01 秒） 

Nangani nilai NULL nganggo skrip PHP

Ing skrip PHP, sampeyan bisa nggunakake pernyataan if...else kanggo ngolah manawa variabel kasebut kosong lan ngasilake pernyataan kondisional sing cocog.

Ing conto ing ngisor iki PHP nyetel variabel $chenweiliang_count banjur nggunakake variabel kasebut kanggo mbandhingake karo kolom chenweiliang_count ing tabel data:

<?
php $ dbhost = ' localhost：3306 ' ; // mysql服务器主机地址

$ dbuser = ' root ' ; // mysql用户名
$ dbpass = ' 123456 ' ; // mysql用户名密码
$ conn = mysqli_connect （$ dbhost ，$ dbuser ，$ dbpass ）;
如果（！$ conn ）{ die （' 连接失败：' 。mysqli_error （$ conn ））;
} // 设置编码，防止中文乱码

mysqli_query （$ conn ，“ set names utf8 ” ）;
 
if （isset （$ chenweiliang_count ））{ $ sql = “ SELECT chenweiliang_author，chenweiliang_count FROM chenweiliang_test_tbl WHER chenweiliang_count = $ chenweiliang_count ” ;
} else { $ sql = “ SELECT chenweiliang_author，chenweiliang_count FROM chenweiliang_test_tbl WHER chenweiliang_count IS NULL ” ;
} mysqli_select_db （$ conn ，'


 chenweiliang ' ）;
$ retval = mysqli_query （$ conn ，$ sql ）;
if （！$ retval ）{ die （' 无法读取数据：' 。mysqli_error （$ conn ））;
} echo ' <h2>陈沩亮博客IS NULL测试<h2> ' ;
echo ' <table border =“1”> <tr> <td>作者</ td> <td>登陆次数</ td> </ tr> ' ;

 $ retval ，MYSQL_ASSOC ））{ echo “ <tr> ” 。
 “ <td> {$ row ['chenweiliang_author']} </ td> ” 。
 “ <td> {$ row ['chenweiliang_count']} </ td> ” 。
 “ </ tr> ” ;
} echo ' </ table> ' ;
mysqli_close （$ conn ）;
?>