システムをアップグレードし、私が取り組んでいるWebアプリケーションのためにMySql 5.7.9とphpをインストールしました。動的に作成されるクエリがあり、MySqlの古いバージョンで実行すると、問題なく動作します。 5.7にアップグレードしてから、このエラーが発生しました:
SELECTリストの式1がGROUP BY句に含まれず、かつ含まれる 非集計カラム 'support_desk.mod_users_groups.group_id' である。 GROUPBY句のカラムに機能的に依存しない、これは sql_mode=only_full_group_by と互換性がありません。
Mysql 5.7のマニュアルページにあるServer SQL Modesというトピックに注目してください。
これが悩みの種であるクエリです:
SELECT mod_users_groups.group_id AS 'value',
group_name AS 'text'
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id
WHERE mod_users_groups.active = 1
AND mod_users_groups.department_id = 1
AND mod_users_groups.manage_work_orders = 1
AND group_name != 'root'
AND group_name != 'superuser'
GROUP BY group_name
HAVING COUNT(`user_id`) > 0
ORDER BY group_name
この問題についていくつかググってみましたが、クエリを修正するために何をすればいいのか、only_full_group_by
を十分に理解できていません。only_full_group_by`オプションをオフにすればいいのでしょうか、それとも他に何か必要なものがあるのでしょうか?
もっと詳しい情報が必要な場合は教えてください。
以下のように実行することで、only_full_group_by
の設定を無効にしてみてください:
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
MySQL 8 は NO_AUTO_CREATE_USER
を受け付けないため、これを削除する必要があります。
GROUP BYに
group_id`を追加するだけですね。
GROUP BYの一部でないカラムをSELECT
する場合、グループ内にそのカラムの複数の値が存在する可能性がありますが、結果には1つの値しか入れるスペースがありません。そこで、データベースは、これらの複数の値を1つの値にする方法を正確に指示する必要があります通常。一般的に、これは COUNT()
, SUM()
, MAX()
などの集計関数を用いて行われます。私が 通常 と言ったのは、他の一般的なデータベースシステムのほとんどがこれを主張しているからです。しかし、バージョン 5.7 以前の MySQL では、デフォルトの動作はより寛容で、文句を言わず、任意に 任意の値 を選択することができます!また、MySQL には ANY_VALUE()
関数があり、以前と同じ動作が本当に必要な場合は、この問題に対する別の解決策として使用することができます。この柔軟性は、非決定的であるという代償を伴うので、それを必要とする非常に良い理由がない限り、私はこれを勧めません。MySQLは現在、正当な理由によりonly_full_group_by
の設定をデフォルトでオンにしています。
では、なぜ上記のようなシンプルな答えになったのでしょうか。私はいくつかの仮定をしました:
group_id
はユニークである。合理的に思えるが、結局は 'ID' なのである。
group_name
もユニークである。これはあまり妥当な仮定ではないかもしれません。もしそうでなく、重複した group_name
があった場合、私のアドバイスに従って group_id
を GROUP BY
に追加すると、同じ名前のグループが結果の中に別の行を持つことになり、以前よりも多くの結果が得られるかもしれません。私としては、データベースが任意に値を選択したために、重複したグループが隠されてしまうよりはましだと考えています!
また、複数のテーブルがある場合は、すべてのカラムをテーブル名または別名で修飾するのがよい習慣です。
SELECT
g.group_id AS 'value',
g.group_name AS 'text'
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id
WHERE g.active = 1
AND g.department_id = 1
AND g.manage_work_orders = 1
AND g.group_name != 'root'
AND g.group_name != 'superuser'
GROUP BY
g.group_name,
g.group_id
HAVING COUNT(d.user_id) > 0
ORDER BY g.group_name
他の回答で説明されているように警告メッセージをオフにするか、何が起こっているのかを理解して修正できます。
MySQL 5.7.5以降、デフォルトのSQLモードにはONLY_FULL_GROUP_BYが含まれますつまり、行をグループ化してからそのグループから何かを選択するときは、明示的にどの行を選択するかを指定する必要がありますから。 。。
Mysqlは、探しているグループの行を知る必要があります。これにより、2つのオプションが提供されます。
-グループステートメント rect.color、rect.value
に目的の列を追加することもできます。これは、場合によっては必要なものになる可能性があります。そうしないと、不要な同じ色の重複結果が返されます。
-mysqlの集約関数を使用して、 AVG()
MIN()``
MAX()[完全なリスト][3]などのグループ内で探している行を示すこともできます。 -最後に、グループ内のすべての結果が同じであることが確実な場合は、**
ANY_VALUE()` **を使用できます。 doc。
もし、現在のクエリに変更を加えない場合は、以下の手順で行ってください。
1.あなたのボックスにvagrant sshを入れる
2.タイプしてください:sudo vim /etc/mysql/my.cnf
と入力する。
3.ファイルの一番下までスクロールし、A
と入力して挿入モードに入ります。
4.コピー&ペースト
[mysqld]です。
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
5.入力モードを終了するには、esc
と入力します。
6.:wqと入力して保存し、vimを終了します。 7.sudo service mysql restart
と入力し、MySQLを再起動する。
ANY_VALUE()
を使用して、非集計列を参照します。。
SELECT name, address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works
ONLY_FULL_GROUP_BY
を無効にすることなく、同じ効果を実現できます。ANY_VALUE()
を使用して、非集計列を参照します。 。 。 ... 。 選択リストの非集計アドレス列が「GROUP BY」句で指定されていないため、このクエリは「ONLY_FULL_GROUP_BY」を有効にすると無効になる場合があります。 。 SELECT名前、住所、MAX(age)FROM t GROUP BY名前;。 。 ... 。 特定のデータセットについて、各名前値が実際に一意にアドレス値を決定することがわかっている場合、アドレスは機能的に名前に依存します。 MySQLにクエリを受け入れるように指示するには、ANY_VALUE()
関数を使用できます。 。 SELECT名、ANY_VALUE(住所)、MAX(age)FROM t GROUP BY名;。
このエラーについて説明しようと思います。
。
MySQL 5.7.5以降、オプション ONLY_FULL_GROUP_BY
がデフォルトで有効になっています。
。
したがって、standart SQL92以前によると:
。
選択リスト、HAVING条件などのクエリは許可されません。 またはORDER BYリストは、どちらの名前も付けられていない非集計列を参照します。 GROUP BY句では、機能的に依存していません(一意に。 によって決定されます)GROUP BY列。
(ドキュメントで詳細を読む)。
したがって、例:
SELECT * FROM `users` GROUP BY `name`;
上記のクエリを実行すると、エラーメッセージが表示されます。
#1055-SELECTリストの式#1はGROUP BY句に含まれておらず、集計されていない列 'testsite.user.id'が含まれていますが、含まれていません。 GROUP BY句の列に機能的に依存します。これは。 sql_mode = only_full_group_byと互換性がありません。
なぜ?
。
MySQLは正確に理解していないため、グループ化されたレコードから取得する特定の値、これがポイントです。
。
つまりこのレコードを「ユーザー」テーブル:
にあるとします。
。
。
そして、あなたは上記の無効なクエリショーを実行します。
。
そして、上に表示されるエラーが表示されます。これは、「John」という名前のレコードが3つあり、それはすばらしいことですが、それらすべてに異なる「email」フィールド値があるためです。
。
したがって、MySQLは、結果のグループ化されたレコードで返すものを単に理解していません。
。
この問題を修正するには、クエリを次のように変更するだけです:
。
SELECT `name` FROM `users` GROUP BY `name`
また、SELECTセクションにさらにフィールドを追加することもできますが、フィールドが集約されていない場合は、それを行うことはできませんが、使用できる松葉 ⁇ があります(ただし、あまり推奨されません)。
SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`
。。
では、「ANY_VALUE」の使用を強くお勧めしない理由を尋ねてください?
。
MySQLはグループ化されたレコードのどの値を取得するかを正確に認識していないため、この関数を使用して、それらのいずれかをフェッチするように要求します(この場合、名前=ジョンの最初のレコードの電子メールがフェッチされました)。
。
正確に言えば、なぜこの振る舞いが存在したいと思うのかについてのアイデアを思いつくことはできません。
。
私の理解がわからない場合は、MySQLでのグループ化がどのように機能するかについてもっと読んでください。それは非常に簡単です。
そして最後に、もう1つシンプルでありながら有効なクエリを示します。
。
利用可能な年齢に応じてユーザー総数をクエリする場合は、このクエリを書き留めておくことができます。
SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;
MySQLルールに従って、これは完全に有効です。
。
等々。
。
問題が正確に何であるかを理解し、それから解決策を書き留めることが重要です。
私はララヴェルホームステッドでララヴェル5.3、mysql 5.7.12を使用しています(0.5.0、私は信じています)。
/etc/mysql/my.cnf
の編集を明示的に設定して反映した後でも:
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
私はまだエラーを受け取っていました。
config / database.php
を true
から false
に変更する必要がありました。
'mysql' => [
'strict' => false, //behave like 5.6
//'strict' => true //behave like 5.7
],
さらに読む:
https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead。 https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2。
wamp 3.0.6または安定版2.5以外の上位バージョンを使用している場合は、この問題に直面する可能性があります。まず、問題はsqlです。 . フィールドにはそれに応じて名前を付ける必要があります。 しかし、それを解決できる別の方法があります。 ⁇ の緑のアイコンをクリックします。 mysql-> mysql settings-> sql_mode-> none。 またはコンソールからデフォルト値を変更できます。
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
macの場合:
1.デフォルトのmy-default.cnfを/etc/my.cnfにコピーします。
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
2.お気に入りのエディターを使用してmy.cnfでsql_modeを変更し、これに設定します。
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
3.MySQLサーバーを再起動します。
mysql.server restart
これが私が問題全体を理解するのに役立ったものです。
1。 https://stackoverflow.com/a/20074634/1066234。 2。 https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html。
そして、問題のあるクエリの次の別の例。
問題:。
SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND cookieid = #
これを最後に追加することで解決:。
GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress
注:PHPのエラーメッセージを参照してください。問題がどこにあるかがわかります。
例:
MySQLクエリエラー1140:GROUP BYなしの集約クエリでは、SELECTリストの式#4に非集計列 'db.gameplay.timestamp'が含まれています。これは、sql_mode = only_full_group_by-Query:SELECT COUNT(*)を試行として、SUM(失効)を経過した合計、ユーザーの質問に WHEREタイムスタンプ> = DATE_SUB(NOW()、INTERVAL 1 DAY)。 AND userid = 1。
この場合、式#4 はGROUP BYにありませんでした。