MySQL で group by を使用すると常にエラー 1055 が発生します (推奨)

MySQL で group by を使用すると常にエラー 1055 が発生するため、原因を確認する必要があります。関連情報を検索し、後で参照できるようにメモを記録します。

sql_mode: 簡単に言えば、MySQL がサポートする必要がある SQL 構文やデータ検証などを定義します。

select @@sql_mode: このコマンドを使用すると、現在のデータベースのsql_modeを表示できます。

mysql> @@sql_mode を選択します。
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
セット内の 1 行 (0.00 秒)

sql_mode の各値の意味を見てみましょう。

ONLY_FULL_GROUP_BY:

GROUP BY 集計操作の場合、SELECT 内の列が GROUP BY に表示されない場合は、列が GROUP BY 句にないため、この SQL は不正であると見なされます。

only_full_group_by のため、MySQL で group by ステートメントを正しく使用したい場合、tb1 group by column1 から column1 のみを選択できます (つまり、group by フィールドのみが表示され、その他はすべてエラー 1055 を報告します)。

例:

mysql> tt1 から * を選択します。
+----+-------+--------+
| ID | 名前 | 性別 |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | カイ | 0 |
| 4 | 張 | 0 |
| 5 | 李 | 1 |
| 6 | 王 | 1 |
+----+-------+--------+
セット内の 6 行 (0.00 秒)
 
mysql> tt1 グループから id、name を名前で選択します。
エラー 1055 (42000):

mysql> tt1 から * を選択します。
+----+-------+--------+
| ID | 名前 | 性別 |
+----+-------+--------+
| 1 | xiong | 0 |
| 2 | ying | 0 |
| 3 | カイ | 0 |
| 4 | 張 | 0 |
| 5 | 李 | 1 |
| 6 | 王 | 1 |
+----+-------+--------+
セット内の 6 行 (0.00 秒)
mysql> tt1 グループから名前を名前で選択します。
+-------+
| 名前 |
+-------+
| カイ |
| 李 |
|王|
| シオン |
| 英 |
| 張 |
+-------+
セット内の 6 行 (0.00 秒)

したがって、group byを正しく使用したい場合は、only_full_group_byを削除する必要があります。

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY','')); このステートメントを使用して only_full_group_by をスペースに置き換え、mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); を使用できるようになります。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 
mysql> tt1 グループから id、name を名前で選択します。
+----+-------+
| ID | 名前 |
+----+-------+
| 3 | カイ |
| 5 | 李 |
| 6 | 王 |
| 1 | シオン |
| 2 | 英 |
| 4 | 張 |
+----+-------+
セット内の 6 行 (0.00 秒)

ただし、この方法は一時的な変更に過ぎません。設定ファイルmy.iniを変更することができます。

sql_mode = STRICT_TRANS_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES:

このモードでは、トランザクション テーブルに値を挿入できない場合、現在の操作が中断され、非トランザクション テーブルには制限が課せられません。

日付にゼロがない:

厳密モードでは、月または日の部分が 0 の日付は受け入れられません。 IGNORE オプションを使用すると、同様の日付に「0000-00-00」が挿入されます。非厳密モードでは、日付は受け入れられますが、警告が生成されます。

日付なし:

厳密モードでは、「0000-00-00」を有効な日付として考慮しません。 IGNORE オプションを使用すると、ゼロの日付を挿入することもできます。非厳密モードでは日付は受け入れられますが、警告が生成されます

ゼロ除算エラー:

厳密モードでは、INSERT または UPDATE 中にゼロ除算 (または MOD(X, 0)) を行うとエラーが発生します (それ以外の場合は警告が発生します)。このモードが指定されていない場合、MySQL はゼロ除算時に NULL を返します。 INSERT IGNORE または UPDATE IGNORE で使用すると、MySQL はゼロ除算の警告を生成しますが、操作の結果は NULL になります。

自動作成ユーザーなし:

パスワードも指定されていない限り、GRANT によって新しいユーザーが自動的に作成されるのを防ぎます。

エンジン代替なし:

必要なストレージ エンジンが無効になっているか、コンパイルされていない場合は、エラーがスローされます。この値が設定されていない場合は、代わりにデフォルトのストレージ エンジンが使用され、例外がスローされます。

3. MySQL 5.0 以降のバージョンでは、ANSI、TRADITIONAL、STRICT_TRANS_TABLES の 3 つの sql_mode モードがサポートされていると言われています。

1. ANSI モード: ルーズ モード。標準 SQL との一貫性を高めるために構文と動作を変更します。挿入されたデータを確認します。定義されたタイプまたは長さに準拠していない場合は、保存する前にデータ型を調整するか切り捨て、警告を発します。この記事の冒頭で述べたエラーについては、まず sql_mode を ANSI モードに設定してデータを挿入できるようにし、除数が 0 であるフィールド値についてはデータベースによって NULL 値に置き換えられます。現在のデータベース モードを ANSI モードに設定します。

 mysql> @@sql_mode を ANSI に設定します。

2. 伝統的モード: 厳密モード。MySQL データベースにデータを挿入するときに、誤ったデータが挿入されないように厳密なデータ検証が実行されます。

error 警告だけでなくエラーも表示されます。トランザクションに使用すると、トランザクションはロールバックされます。 注意: エラーが見つかった場合は、INSERT/UPDATE を直ちに中止してください。非トランザクション ストレージ エンジンを使用している場合、エラー発生前に行われたデータ変更は「ロールオーバー」されず、「部分的に実行された」更新となるため、これは望ましいことではありません。

現在のデータベース モードを TRADITIONAL モードに設定します。

mysql> @@sql_mode を TRADITIONAL に設定します。

3. STRICT_TRANS_TABLES モード: 厳密なモードでは、厳密なデータ検証が実行され、不正なデータは挿入できず、エラーが報告されます。指定された値をトランザクション テーブルに挿入できない場合、ステートメントは中止されます。非トランザクション テーブルの場合、値が単一行ステートメントまたは複数行ステートメントの行 1 に表示されると、ステートメントは破棄されます。現在のデータベース モードを STRICT_TRANS_TABLES モードに設定します。

mysql> @@sql_mode=STRICT_TRANS_TABLES を設定します。

最良モデルや最悪モデルはなく、最も適したモデルがあるだけです。実際の状況に応じて、最も適切なモードを選択する必要があります。 ！ ！

もう 1 つのポイントは、ここでのデータベース モードへの変更はすべてセッション レベルで 1 回限りであり、閉じて再度開いた場合はカウントされないことです。 ！ ！

設定ファイルから設定することもできます: vim /etc/my.cnf

my.cnf (my.ini) に次の設定を追加します。

[mysqld]
sql_mode = '希望するモード'

要約する

以上、MySQL で group by を使用する際に必ず発生するエラー 1055 についてご紹介しました。参考になれば幸いです。