MySQL數(shù)據(jù)庫中不同數(shù)據(jù)類型字段關(guān)聯(lián)后結(jié)果居然有這么大差異？
一、案例
1、數(shù)據(jù)庫中先創(chuàng)建表及數(shù)據(jù)
-- 創(chuàng)建tb1
CREATE TABLE tb1 (
id BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR (20)
);
INSERT INTO tb1 (id, NAME)
VALUES
(1459066134882947196, 'na1'), (1459066134882947172, 'cccb'), (1459066134882947163, 'tttttttn'), (1459066134882947198, 'acqada');
-- 創(chuàng)建tb2
CREATE TABLE tb2 (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, pid VARCHAR (20), c1 VARCHAR (10)
);
INSERT INTO tb2 (pid, c1)
VALUES
('1459066134882947196', 'cs'), (1459066134882947197, 'tt');tb1 的id表為bigint,tb2表pid字段類型為varchar。
2、進(jìn)行左連接查詢
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.id=b.`pid`
WHERE a.id =1459066134882947196查詢結(jié)果如下：
結(jié)果為非預(yù)期，因?yàn)?個表的關(guān)聯(lián)字段的內(nèi)容并不相同。
3、使用內(nèi)連接
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON a.id=b.`pid`
WHERE a.id =1459066134882947196使用內(nèi)連接后，結(jié)果也不正確。
4、不加where條件的左連接
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.id=b.`pid`查詢結(jié)果如下：
關(guān)聯(lián)后確實(shí)是非預(yù)期的結(jié)果。
5、不加where條件的內(nèi)連接
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON a.id=b.`pid`查詢結(jié)果為：
此時不加where條件的內(nèi)連接的結(jié)果卻是正確的、
二、解決方案
解決此問題的方法主要是解決兩個關(guān)聯(lián)字段的類型不同的問題，可以有2種方式
1、顯式類型轉(zhuǎn)換
在關(guān)聯(lián)的時候顯式地進(jìn)行字段類型轉(zhuǎn)換，例如：
SELECT a.id,b.pid FROM tb1 a LEFT JOIN tb2 b
ON CAST(a.`id` AS CHAR)=b.`pid`
WHERE a.id=1459066134882947196結(jié)果如下：
此時結(jié)果正確。
內(nèi)連接結(jié)果也正確。
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON CAST(a.`id` AS CHAR)=b.`pid`
WHERE a.id =14590661348829471962、改變字段類型（推薦）
如果兩張表的數(shù)據(jù)量較大，使用顯式的字段類型轉(zhuǎn)換（包括當(dāng)前隱式字段類型轉(zhuǎn)換）都將導(dǎo)致關(guān)聯(lián)時不能使用索引，影響性能。因此建議在表設(shè)計時就將存在關(guān)聯(lián)關(guān)系的字段類型設(shè)置為類型相同（字符類型時字符集及排序規(guī)則也一致）例如:
ALTER TABLE tb2 MODIFY pid BIGINT;修改后再查詢看一下結(jié)果：
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.`id`=b.`pid`
WHERE a.id =1459066134882947196結(jié)果正確：
三、小結(jié)
此情況的出現(xiàn)是因?yàn)閮杀淼年P(guān)聯(lián)字段類型不同時進(jìn)行字段類型轉(zhuǎn)換導(dǎo)致。bigint與varchar轉(zhuǎn)換過程中字段精度出現(xiàn)問題，實(shí)際超過int最大值的數(shù)據(jù)（2147483647,即2^31 - 1）的數(shù)據(jù)被截斷為2^31 - 1處理，因?yàn)閮杀磉M(jìn)行左關(guān)聯(lián)時，存在異常。
從上面的過程中，也發(fā)現(xiàn)左連接過程與內(nèi)連接的過程中的中間數(shù)據(jù)結(jié)果（1.4及1.5中）也不同。
