MySQLの暗黙的な変換について話す

作業の過程で、暗黙的な変換が発生するケースが数多くあります。暗黙的な変換は、クエリの速度低下を引き起こすだけでなく、不正確なデータの原因にもなります。この記事では、生産現場で遭遇するいくつかのケースを使用して、これを説明します。

基礎

比較演算の原理については、MySQL の公式ドキュメントで説明されています: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

判定シンボルの2辺のうち1つがNULLの場合、安全な同値判定<=>が使用されない限り、結果はNULLになります。

(なし) 05:17:16 >select null = null;
+-------------+
| ヌル = ヌル |
+-------------+
| NULL |
+-------------+
セット内の 1 行 (0.00 秒)

(なし) 05:34:59 >select null <=> null;
+---------------+
| ヌル <=> ヌル |
+---------------+
| 1 |
+---------------+
セット内の 1 行 (0.00 秒)

(なし) 05:35:51 >select null != 1;
+-----------+
| null != 1 |
+-----------+
| NULL |
+-----------+
セット内の 1 行 (0.00 秒)

左側と右側が同じ型であるかどうかを判断する方法。たとえば、両方とも文字列である場合は、文字列として比較します。数値の場合は数値として比較が行われます。

注: 文字列と数値のより一般的な比較では、文字列フィールドがインデックスフィールドである場合、MySQL はインデックスを通じてデータを見つけることができません。次に例を示します。

(なし) 05:39:42 >select 1='1';
+-------+
| 1='1' |
+-------+
| 1 |
+-------+
セット内の 1 行 (0.00 秒)

(なし) 05:39:44 >select 1='1A';
+--------+
| 1='1A' |
+--------+
| 1 |
+--------+
セットに 1 行、警告 1 件 (0.00 秒)

(なし) 05:39:47 >select 1='1 '; ##1 の後にスペースがあります+--------+
| 1='1' |
+--------+
| 1 |
+--------+
セット内の 1 行 (0.00 秒)

MySQL では、数値 1 は '1'、'1_'、および '1A' と等しいとみなされるため、インデックスバイナリ検索では特定の値を正確に見つけることはできません。

16 進数は文字列をバイナリ文字列として比較します。
シンボルの左側がタイムスタンプ型または日付時刻型で、右側が定数であるかどうかを判断する方法。比較する前に、定数は時刻型に変換されます。

暗黙的な変換

フィールドタイプは異なります

それ以外の場合、引数は浮動小数点（実数）として比較されます。

上記の他の種類の比較に加えて、システムは比較のためにフィールドとパラメータを浮動小数点型に変換します。浮動小数点数（または浮動小数点数に変換された値）を使用した比較は、そのような数値が正確ではないため、近似値になります。次の2つの例を見てください

> '190325171202362933' = 190325171202362931 を選択します。
+------------------------------------------+
| '190325171202362933' = 190325171202362931 |
+------------------------------------------+
| 1 |
+------------------------------------------+
セット内の 1 行 (0.00 秒)

> '190325171202362936' = 190325171202362931 を選択します。
+------------------------------------------+
| '190325171202362936' = 190325171202362931 |
+------------------------------------------+
| 1 |
+------------------------------------------+
セット内の 1 行 (0.00 秒)

直感的に等しくない値も、等しいと判断されて実際には 1 として返されます。これにより、インデックスが使用できず、結果データが不正確になるという2つの問題が発生します。

> '190325171202362931'+0.0 を選択します。
+--------------------------+
| '190325171202362931'+0.0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
セット内の 1 行 (0.00 秒)

> '190325171202362936'+0.0 を選択します。
+--------------------------+
| '190325171202362936'+0.0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
セット内の 1 行 (0.00 秒)

上記の値を浮動小数点数に変換すると、両方とも 1.9032517120236294e17 となるので、等しい場合は真となり、True が返されます。

inパラメータには複数の型が含まれます

具体的なケースについては、前回の記事MySQL最適化ケース1を参照してください。これは、inセット内のデータ型が異なり、実行プランがインデックスを使用しない場合です。

皆さんにお勧めするTaobao MySQL Monthly Report（http://mysql.taobao.org/monthly/2017/12/06/）に、まさにこれと同じような事例があります。要するに、INの入り口で判断が下されるということです。IN内のフィールドタイプが不適合な場合、インデックスは使用できないとみなされます。

arg_types_compatible の割り当てロジックは次のとおりです。

(type_cnt == 1)の場合 
    arg_types_compatible = TRUE;

つまり、IN リストに複数のフィールドタイプが表示される場合、それらのタイプは互換性がないとみなされ、インデックスは使用できません。

一貫性のない文字セットタイプ

環境の準備:

テーブル `t1` を作成します (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) デフォルト NULL,
`c2` varchar(50) デフォルト NULL,
主キー (`id`)、
キー `idx_c1` (`c1`)、
キー `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 デフォルト CHARSET=utf8;


テーブル `t2` を作成します (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) デフォルト NULL,
`c2` varchar(50) デフォルト NULL,
主キー (`id`)、
キー `idx_c1` (`c1`)、
キー `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 デフォルト CHARSET=utf8mb4;


t1(c1,c2) に値('a','a'),('b','b'),('c','c'), を挿入します。
('d','d'),('e','e');
t2(c1,c2) に値('a','a'),('b','b'),('c','c'), を挿入します。
('d','d'),('e','e');

テスト結果