MySQL數據庫中不同數據類型字段關聯后結果居然有這么大差異？
作者：懂點IT的耿小廚
在數據庫的世界里，數據的連接操作是至關重要的。但在處理關聯表的字段的數據類型不同時，得到的結果經常會出乎預料。
一、案例
1、數據庫中先創建表及數據
-- 創建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');
-- 創建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、進行左連接查詢
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.id=b.`pid`
WHERE a.id =1459066134882947196查詢結果如下：
結果為非預期，因為2個表的關聯字段的內容并不相同。
3、使用內連接
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON a.id=b.`pid`
WHERE a.id =1459066134882947196使用內連接后，結果也不正確。
4、不加where條件的左連接
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.id=b.`pid`查詢結果如下：
關聯后確實是非預期的結果。
5、不加where條件的內連接
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON a.id=b.`pid`查詢結果為：
此時不加where條件的內連接的結果卻是正確的、
二、解決方案
解決此問題的方法主要是解決兩個關聯字段的類型不同的問題，可以有2種方式
1、顯式類型轉換
在關聯的時候顯式地進行字段類型轉換，例如：
SELECT a.id,b.pid FROM tb1 a LEFT JOIN tb2 b
ON CAST(a.`id` AS CHAR)=b.`pid`
WHERE a.id=1459066134882947196結果如下：
此時結果正確。
內連接結果也正確。
SELECT a.id,b.pid
FROM tb1 a JOIN tb2 b
ON CAST(a.`id` AS CHAR)=b.`pid`
WHERE a.id =14590661348829471962、改變字段類型（推薦）
如果兩張表的數據量較大，使用顯式的字段類型轉換（包括當前隱式字段類型轉換）都將導致關聯時不能使用索引，影響性能。因此建議在表設計時就將存在關聯關系的字段類型設置為類型相同（字符類型時字符集及排序規則也一致）例如:
ALTER TABLE tb2 MODIFY pid BIGINT;修改后再查詢看一下結果：
SELECT a.id,b.pid
FROM tb1 a LEFT JOIN tb2 b
ON a.`id`=b.`pid`
WHERE a.id =1459066134882947196結果正確：
三、小結
此情況的出現是因為兩表的關聯字段類型不同時進行字段類型轉換導致。bigint與varchar轉換過程中字段精度出現問題，實際超過int最大值的數據（2147483647,即2^31 - 1）的數據被截斷為2^31 - 1處理，因為兩表進行左關聯時，存在異常。
從上面的過程中，也發現左連接過程與內連接的過程中的中間數據結果（1.4及1.5中）也不同。
責任編輯：姜華
來源：
數據庫干貨鋪
