Olen päivittänyt järjestelmääni ja asentanut MySql 5.7.9:n ja php:n web-sovellusta varten, jonka parissa työskentelen. Minulla on kysely, joka luodaan dynaamisesti, ja kun se suoritetaan MySql:n vanhemmissa versioissa, se toimii hyvin. Päivityksen jälkeen versioon 5.7 saan tämän virheen:
SELECT-luettelon ilmaus #1 ei ole GROUP BY -lausekkeessa ja sisältää ei-aggregoitua saraketta 'support_desk.mod_users_groups.group_id' joka on ei ole toiminnallisesti riippuvainen GROUP BY -lausekkeen sarakkeista; tämä on ei ole yhteensopiva sql_mode=only_full_group_by-koodin kanssa.
Huomaa Mysql 5.7:n käsikirjan sivu aiheesta Server SQL Modes.
Tämä on kysely, joka aiheuttaa minulle ongelmia:
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
Googlasin hieman asiaa, mutta en ymmärrä only_full_group_by
tarpeeksi selvittääkseni, mitä minun on tehtävä kyselyn korjaamiseksi. Voinko vain poistaa only_full_group_by
-vaihtoehdon käytöstä, vai onko minun tehtävä jotain muuta?
Kerro minulle, jos tarvitset lisätietoja.
Voit yrittää poistaa only_full_group_by
-asetuksen käytöstä seuraavalla tavalla:
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 ei hyväksy NO_AUTO_CREATE_USER
, joten se on poistettava.
Lisäisin vain group_id
-ryhmän GROUP BY
yhteyteen.
Kun valitset sarakkeen, joka ei ole osa GROUP BY
-ryhmää, ryhmissä voi olla useita arvoja kyseiselle sarakkeelle, mutta tuloksissa on tilaa vain yhdelle arvolle. Tietokannalle on siis tavallisesti kerrottava, miten nämä useat arvot saadaan yhdeksi arvoksi. Yleensä tämä tehdään aggregaattifunktiolla, kuten COUNT()
, SUM()
, MAX()
jne.... Sanon tavallisesti, koska useimmat muut suositut tietokantajärjestelmät vaativat tätä. Kuitenkin MySQL:ssä ennen versiota 5.7 oletuskäyttäytyminen on ollut anteeksiantavampaa, koska se ei valita mielivaltaisesti mitään arvoa! Siinä on myös ANY_VALUE()
-funktio, jota voidaan käyttää toisena ratkaisuna tähän kysymykseen, jos todella tarvitset samaa käyttäytymistä kuin ennen. Tämän joustavuuden hintana on, että se on epämääräinen, joten en suosittele sitä, ellei sinulla ole erittäin hyvää syytä sen tarvitsemiseen. MySQL ottaa nyt oletusarvoisesti käyttöön only_full_group_by
-asetuksen hyvistä syistä, joten on parasta tottua siihen ja tehdä kyselyistä sen mukaisia.
Miksi siis yllä oleva yksinkertainen vastaukseni? Olen tehnyt pari oletusta:
group_id
on yksilöllinen. Vaikuttaa järkevältä, onhan kyseessä 'ID'.
group_name
on myös yksilöllinen. Tämä ei ehkä olekaan niin järkevä oletus. Jos näin ei ole ja sinulla on joitakin päällekkäisiä group_names
ja noudatat sitten neuvoani lisätä group_id
GROUP BY
:hen, saatat huomata, että saat nyt enemmän tuloksia kuin ennen, koska samannimisillä ryhmillä on nyt erilliset rivit tuloksissa. Minusta tämä olisi parempi kuin se, että nämä päällekkäiset ryhmät olisivat piilossa, koska tietokanta on hiljaa valinnut arvon mielivaltaisesti!
On myös hyvä käytäntö määritellä kaikki sarakkeet niiden taulukkonimellä tai aliaksella, kun mukana on useampi kuin yksi taulukko...
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
Jos et halua tehdä muutoksia nykyiseen kyselyyn, noudata seuraavia ohjeita -
vagrant ssh boxiisi
Kirjoita: sudo vim /etc/mysql/my.cnf
.
Selaa tiedoston alareunaan ja kirjoita A
siirtyäksesi insert-tilaan.
Kopioi ja liitä
[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
Kirjoita esc
poistuaksesi syöttötilasta
Kirjoita :wq
tallentaaksesi ja sulkeaksesi vim.
Kirjoita sudo service mysql restart
käynnistääksesi MySQL:n uudelleen.