Saya telah meng-upgrade sistem saya dan telah menginstal MySql 5.7.9 dengan php untuk aplikasi web yang saya kerjakan. Saya memiliki permintaan yang dibuat secara dinamis, dan ketika berjalan di versi MySql bekerja dengan baik. Sejak upgrade ke 5.7 saya mendapatkan error ini:
Ekspresi #1 dari daftar PILIH tidak di GROUP BY clause dan berisi nonaggregated kolom 'support_desk.mod_users_groups.group_id' yang tidak fungsional tergantung pada kolom GROUP BY klausa; ini adalah tidak kompatibel dengan sql_mode=only_full_group_by
Catatan halaman Manual untuk Mysql 5.7 pada topik SQL Server Mode.
Ini adalah permintaan yang memberi saya kesulitan:
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
Saya melakukan googling tentang masalah ini, tapi aku don't memahami only_full_group_by
cukup untuk mencari tahu apa yang harus saya lakukan untuk memperbaiki query. Saya dapat hanya mematikan only_full_group_by
pilihan, atau ada sesuatu yang lain yang harus saya lakukan?
Biarkan aku tahu jika anda memerlukan informasi lebih lanjut.
Anda dapat mencoba untuk menonaktifkan only_full_group_by
pengaturan dengan mengeksekusi berikut:
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 tidak menerima NO_AUTO_CREATE_USER
sehingga perlu dihapus.
Saya hanya akan menambahkan group_id
ke GROUP BY
.
Ketika PILIH a'ing kolom yang bukan merupakan bagian dari
GROUP BYmungkin ada beberapa nilai untuk kolom tersebut dalam kelompok-kelompok, tetapi hanya ada ruang untuk satu nilai dalam hasil. Jadi, database *biasanya* kebutuhan akan mengatakan dengan tepat bagaimana untuk membuat orang-orang banyak nilai ke dalam satu nilai. Umumnya, hal ini dilakukan dengan fungsi agregat seperti
COUNT(),
JUMLAH ' (),
MAX()dll... saya katakan *biasanya* karena paling populer lainnya sistem database bersikeras ini. Namun, di MySQL sebelum versi 5.7 perilaku default telah lebih pemaaf karena itu tidak akan mengeluh dan kemudian sewenang-wenang memilih *nilai*! Ia juga memiliki sebuah
ANY_VALUE()fungsi yang dapat digunakan sebagai solusi lain untuk pertanyaan ini jika anda benar-benar diperlukan perilaku yang sama seperti sebelumnya. Fleksibilitas ini datang pada biaya, karena itu adalah non-deterministik, jadi saya tidak akan merekomendasikan hal ini kecuali anda memiliki alasan yang sangat baik untuk membutuhkan itu. MySQL sekarang beralih pada
only_full_group_by` pengaturan secara default untuk alasan yang baik, sehingga's terbaik untuk mendapatkan digunakan untuk itu dan membuat permintaan anda sesuai dengan itu.
Jadi mengapa jawaban sederhana saya di atas? I've dibuat beberapa asumsi:
group_id
adalah unik. Tampaknya masuk akal, itu adalah 'ID' setelah semua.
group_name
juga unik. Ini mungkin tidak seperti asumsi yang masuk akal. Jika hal ini tidak terjadi dan anda memiliki beberapa duplikat group_names
dan kemudian anda mengikuti saran saya untuk menambahkan group_id
ke GROUP BY
, anda mungkin menemukan bahwa anda sekarang mendapatkan hasil yang lebih dari sebelumnya, karena kelompok-kelompok dengan nama yang sama sekarang akan memiliki baris terpisah dalam hasil. Untuk saya, ini akan lebih baik daripada memiliki duplikat ini kelompok tersembunyi karena database diam-diam telah dipilih nilai sewenang-wenang!
It's juga praktik yang baik untuk memenuhi semua kolom dengan nama tabel atau alias ketika ada's lebih dari satu tabel yang terlibat...
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
anda dapat menonaktifkan pesan peringatan seperti yang dijelaskan dalam jawaban yang lain atau anda dapat memahami apa's terjadi dan memperbaikinya.
Seperti MySQL 5.7.5, default SQL mode termasuk ONLY_FULL_GROUP_BY yang berarti ketika anda adalah pengelompokan baris dan kemudian memilih sesuatu yang keluar dari kelompok, anda perlu secara eksplisit mengatakan yang turut pilihan itu harus dibuat dari.
Mysql perlu tahu yang turut dalam kelompok anda'kembali mencari, yang memberi anda dua pilihan
group by rect.warna, rect.nilai
yang dapat menjadi apa yang anda inginkan dalam beberapa kasus, jika tidak akan kembali hasil duplikat dengan warna yang sama yang anda mungkin tidak inginAVG()
MIN()
MAX()
daftar lengkap ANY_VALUE()
jika anda yakin bahwa semua hasil dalam kelompok yang sama. docJika anda don't ingin membuat perubahan dalam permintaan saat ini kemudian ikuti langkah-langkah di bawah ini -
sudo vim /etc/mysql/my.cnf
A
untuk masuk ke mode insert[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
esc
untuk keluar dari mode input:wq
untuk menyimpan dan menutup vim.sudo service mysql restart
untuk me-restart MySQL.Gunakan ANY_VALUE()
untuk merujuk ke nonaggregated kolom.
SELECT name, address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works
Dari MySQL 5.7 docs:
Anda dapat mencapai efek yang sama tanpa menonaktifkan
ONLY_FULL_GROUP_BY
dengan menggunakanANY_VALUE()
untuk merujuk ke nonaggregated kolom....
query Ini mungkin tidak valid dengan
ONLY_FULL_GROUP_BY
diaktifkan karena nonaggregated alamat kolom dalam daftar pilih adalah yang tidak disebutkan dalamGROUP BY
ayat:PILIH nama, alamat, USIA(age) DARI t GROUP BY nama; ...
Jika anda tahu bahwa, untuk suatu set data, nama masing-masing nilai pada fakta unik menentukan nilai alamat, alamat efektif secara fungsional tergantung pada nama. Untuk memberitahu MySQL untuk menerima query, anda dapat menggunakan
ANY_VALUE()
fungsi:PILIH nama, ANY_VALUE(alamat), MAX(usia) DARI t GROUP BY nama;
Saya akan mencoba untuk menjelaskan kesalahan apa ini adalah tentang.
Mulai dari MySQL 5.7.5, opsi ONLY_FULL_GROUP_BY
diaktifkan secara default.
Dengan demikian, menurut standar SQL92 dan sebelumnya:
tidak mengizinkan permintaan untuk yang daftar pilih, MEMILIKI kondisi, atau ORDER DENGAN klik disini lihat nonaggregated kolom yang tidak bernama dalam klausa GROUP BY atau tergantung secara fungsional pada (unik ditentukan oleh) GROUP BY kolom
Jadi, misalnya:
SELECT * FROM `users` GROUP BY `name`;
Anda akan mendapatkan pesan kesalahan setelah mengeksekusi query di atas.
#1055 - Ekspresi #1 dari daftar PILIH tidak di GROUP BY clause dan berisi nonaggregated kolom 'testsite.pengguna.id' yang tidak fungsional tergantung pada kolom GROUP BY klausa; ini adalah tidak kompatibel dengan sql_mode=only_full_group_by
Mengapa?
Karena MySQL tidak benar-benar mengerti, apa nilai-nilai tertentu dari dikelompokkan catatan untuk mengambil, dan ini adalah titik.
I. E. katakanlah anda memiliki data dalam tabel `users:
Dan anda akan mengeksekusi query tidak valid showen di atas.
Dan anda akan mendapatkan error yang ditampilkan di atas, karena, ada 3 catatan dengan nama John
, dan itu bagus, tetapi, mereka semua telah berbeda email
nilai-nilai bidang.
Jadi, MySQL hanya don't memahami mana dari mereka untuk kembali dalam menghasilkan dikelompokkan catatan.
Anda dapat memperbaiki masalah ini, dengan hanya mengubah query seperti ini:
SELECT `name` FROM `users` GROUP BY `name`
Juga, anda mungkin ingin menambahkan lebih banyak bidang untuk MEMILIH bagian, tetapi anda tidak bisa melakukan itu, jika mereka tidak dikumpulkan, tapi ada kruk anda bisa menggunakan (tapi sangat tidak direkomendasikan):
SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`
Sekarang, anda mungkin bertanya, mengapa menggunakan ANY_VALUE
sangat tidak dianjurkan?
Karena MySQL don't tahu persis apa nilai dikelompokkan catatan untuk mengambil, dan dengan menggunakan fungsi ini, anda meminta untuk mengambil salah satu dari mereka (dalam hal ini, email dari record pertama dengan nama = John diambil).
Sebenarnya saya tidak bisa datang dengan ide-ide tentang mengapa anda akan ingin ini perilaku yang ada.
Silakan, jika anda tidak mengerti saya, baca lebih lanjut tentang bagaimana pengelompokan di MySQL bekerja, itu sangat sederhana.
Dan pada akhirnya, di sini adalah salah satu yang lebih sederhana, namun berlaku query.
Jika anda ingin query total pengguna dihitung menurut usia, anda mungkin ingin untuk menuliskan query ini
SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;
Yang sepenuhnya valid, menurut MySQL aturan.
Dan sebagainya.
Hal ini penting untuk memahami apa sebenarnya masalah ini dan hanya kemudian menuliskan solusi.
Saya menggunakan Laravel 5.3, mysql 5.7.12, pada laravel homestead (0.5.0, saya percaya)
Bahkan setelah eksplisit pengaturan editing /etc/mysql/my.cnf
untuk mencerminkan:
[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
Aku masih menerima kesalahan.
Aku harus berubah config/database.php
dari true
menjadi false
:
'mysql' => [
'strict' => false, //behave like 5.6
//'strict' => true //behave like 5.7
],
Bacaan lebih lanjut:
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
Jika anda menggunakan wamp 3.0.6 atau atas versi lain dari stabil 2.5 anda mungkin menghadapi masalah ini, pertama masalah dengan sql . anda harus nama bidang yang sesuai. tapi ada cara lain dengan mana anda dapat menyelesaikannya. klik pada ikon hijau dari wamp. mysql->mysql pengaturan-> sql_mode->tidak ada. atau dari konsol anda dapat mengubah nilai default.
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';
Penambahan garis (sebutkan di bawah ini) dalam file : /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
Bekerja dengan baik bagi saya. Server versi: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)
Untuk mac:
1.Copy standar my-default.cnf /etc/my.cnf
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
2.Perubahan sql_mode di saya.cnf menggunakan editor favorit anda dan mengaturnya untuk 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
3.Restart server MySQL.
mysql.server restart
Ini adalah apa yang membantu saya untuk memahami seluruh masalah:
Dan berikut ini contoh lain dari permintaan bermasalah.
Bermasalah:
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 = #
Soal dengan menambahkan ini ke akhir:
GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress
Catatan: Melihat pesan error di PHP, itu memberitahu anda di mana masalahnya terletak.
Contoh:
query MySQL error 1140: Dalam agregat permintaan tanpa GROUP BY, ekspresi #4 PILIH daftar berisi nonaggregated kolom 'db.gameplay.timestamp'; hal ini tidak sesuai dengan sql_mode=only_full_group_by - Query: SELECT COUNT(*) sebagai upaya, SUM(yang telah berlalu) sebagai elapsedtotal, userid, timestamp, questionid, answerid, SUM(benar) sebagai benar, berlalu, ipaddress DARI gameplay Di MANA timestamp >= DATE_SUB(NOW(), INTERVAL 1 HARI) DAN userid = 1
Dalam hal ini, ekspresi #4 hilang di GROUP BY.
Anda dapat menambahkan indeks unik
untuk group_id
; jika anda yakin bahwa group_id
adalah unik.
Hal ini dapat menyelesaikan kasus anda tanpa memodifikasi query.
Sebuah jawaban terlambat, tapi itu belum disebutkan namun dalam jawaban. Mungkin itu harus lengkap sudah jawaban yang komprehensif yang tersedia. Setidaknya hal itu memecahkan kasus saya ketika saya harus membagi sebuah tabel dengan banyak kolom.
Jika anda memiliki kesalahan ini dengan Symfony menggunakan ajaran query builder, dan jika kesalahan ini disebabkan oleh sebuah orderBy :
Memperhatikan pilih
kolom yang ingin anda groupBy
, dan menggunakan addGroupBy
bukan groupBy
:
$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');
Bekerja pada Symfony3 -
Mohon maaf untuk tidak menggunakan yang tepat anda SQL
Saya menggunakan query ini untuk mengatasi Mysql peringatan.
SELECT count(*) AS cnt, `regions_id`
FROM regionables
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1
catatan penting untuk saya menjadi
count(*) AS cnt