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

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');

テスト結果

まとめ

上記の事例と基礎知識の紹介を通じて、開発者が回り道を回避できることを願っています。SQL を開発して記述するときは、フィールドの型、特に id、xxxid、xxxno などの数値型のように見えても実際には文字型である可能性のあるフィールドの型を明確に定義する必要があります。

以上がMySQLにおける暗黙的な変換の詳細です。MySQLの暗黙的な変換の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • MySQLの整数および文字列インデックスの無効化または暗黙的な変換に関する簡単な説明
  • MySQLの驚くべき暗黙の変換
  • MySQL 5.6 の「暗黙的な変換」によりインデックスが失敗し、データが不正確になる
  • MySQLの暗黙的な変換問題の解決
  • MySQL インデックス無効化の暗黙的な変換の問題

<<:  Windows 2016 Server セキュリティ設定

>>:  Reactは複雑な検索フォームの展開と折りたたみ機能を実装します

推薦する

ウェブページのコアコンテンツ(画像とテキスト)の視覚的表現の紹介

情報の最適化と改良は常にデザインの最初のステップです。 「これは百度アライアンスユーザーエクスペリエ...

MySQL データベース シェル import_table データ インポート

目次MySQL Shell import_table データのインポート1. import_tabl...

HTML テーブルに複雑なテーブル ヘッダーを実装するためのサンプル コード

複雑な表を作成するには HTML を使用します。複雑なテーブルでは通常、td の rowspan 属...

Linux系でよく使われる運用・保守コマンド(まとめ)

目次1. システム監視2. ファイル操作3. ネットワーク通信4. システム管理仕事で必要なLinu...

MySQL pt-slave-restart ツールの使い方の紹介

目次MySQL マスター スレーブ レプリケーション環境を設定する場合、マスター データベースとスレ...

新しい要素を作成する3つの方法のまとめ

1つ目: テキスト/HTML経由var txt1="<h1>テキスト。<...

フィボナッチ数列のJavaScript出力を実装する方法

目次トピック分析する基本的な解決策基本的な再帰再帰最適化要約するトピック私たちが答えなければならない...

Web デザイナーにはどのような知識体系が必要ですか?

製品設計者は、複雑で大規模な製造システムと多様な市場に直面しているため、知識体系には幅広さと深さの両...

MySQLカバーインデックスの詳しい説明

コンセプトインデックスにクエリ要件を満たすすべてのデータが含まれている場合、それはカバーリング イン...

MySQL count(1)、count(*)、count(field)の違い

目次1. COUNTの初見2. COUNT(フィールド)、COUNT(定数)、COUNT(*)の違い...

MySQLカスタム変数の概念と特徴

MySQL カスタム値は、値を保存するための一時的なコンテナです。サーバーへの接続がアクティブである...

IDEA で mysql8.0.3 と mybatis-generator を使用する際に発生するバグ

1. プラグインを追加し、pomファイルの下に次の設定を追加します。 <!-- mybatis...

MySQLデータを復元する2つの方法

1. はじめに少し前、開発者がテスト環境や本番環境で誤った操作をし、データベースを誤って削除/更新し...