MySQL
त्रुटि 1055: ONLY_FULL_GROUP_BY: कुछ समूह द्वारा समूह में नहीं है ...

परिचय

टिप्पणियों

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

ग्रुप बाय का उपयोग और दुरुपयोग

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

item नामक तालिका में पंक्तियों को दिखाएगा, और uses तालिका में संबंधित पंक्तियों की गिनती दिखाएगा। यह अच्छा काम करता है, लेकिन दुर्भाग्य से यह मानक SQL-92 नहीं है।

क्यों नहीं? क्योंकि GROUP BY SELECT क्लाज (और ORDER BY क्लॉज) प्रश्नों में कॉलम में वे कॉलम होने चाहिए

1. GROUP BY क्लॉज में उल्लिखित है, या
2. कुल कार्यों जैसे COUNT() , MIN() , और इसी तरह।

इस उदाहरण के SELECT क्लॉज में item.name का उल्लेख किया item.name , एक कॉलम जो उन मानदंडों में से किसी से भी नहीं मिलता है। यदि SQL मोड में ONLY_FULL_GROUP_BY शामिल है तो MySQL 5.6 और पहले इस क्वेरी को अस्वीकार कर ONLY_FULL_GROUP_BY ।

इस तरह से GROUP BY क्लॉज को बदलकर SQL-92 मानक का पालन करने के लिए इस उदाहरण क्वेरी को बनाया जा सकता है।

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

बाद में SQL-99 मानक समूह कुंजी से चयनित स्तंभों को छोड़ देने के लिए एक SELECT कथन की अनुमति देता है यदि DBMS उनके और समूह कुंजी स्तंभों के बीच कार्यात्मक निर्भरता साबित कर सकता है। क्योंकि item.name कार्यात्मक रूप से item.item_id पर निर्भर है, प्रारंभिक उदाहरण मान्य SQL-99 है। MySQL ने संस्करण 5.7 में एक कार्यात्मक निर्भरता कहावत प्राप्त की। मूल उदाहरण ONLY_FULL_GROUP_BY तहत काम करता है।

अप्रत्याशित परिणाम वापस करने के लिए ग्रुप बाय मिसिंग: मर्फीज लॉ

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

आइटम नामक तालिका में पंक्तियों को दिखाएगा, और उपयोग की जाने वाली तालिका में संबंधित पंक्तियों की गिनती दिखाएगा। यह एक स्तंभ का मान भी दिखाता है, जिसे uses.category कहा जाता है।

यह क्वेरी MySQL ( ONLY_FULL_GROUP_BY ध्वज दिखाई देने से पहले) में काम करती है। यह GROUP BY MySQL के नॉनस्टैंडर्ड एक्सटेंशन का उपयोग करता है।

लेकिन क्वेरी में एक समस्या है: यदि uses तालिका में कई पंक्तियाँ JOIN क्लॉज में ON कंडीशन से मेल खाती हैं, तो MySQL उन कॉलमों में से केवल एक से category कॉलम लौटाता है। कौन सी पंक्ति? क्वेरी के लेखक और एप्लिकेशन के उपयोगकर्ता को पहले से यह पता नहीं चलता है। औपचारिक रूप से, यह अप्रत्याशित है : MySQL अपने इच्छित किसी भी मूल्य को वापस कर सकता है।

अप्रत्याशित एक महत्वपूर्ण अंतर के साथ यादृच्छिक की तरह है। समय-समय पर परिवर्तन के लिए एक यादृच्छिक विकल्प की उम्मीद की जा सकती है। इसलिए, यदि कोई विकल्प यादृच्छिक था, तो आप डीबगिंग या परीक्षण के दौरान इसका पता लगा सकते हैं। अप्रत्याशित परिणाम बदतर है: जब तक आप क्वेरी का उपयोग नहीं करते, तब तक MySQL एक ही परिणाम देता है । कभी-कभी यह MySQL सर्वर का एक नया संस्करण होता है जो एक अलग परिणाम का कारण बनता है। कभी-कभी यह बढ़ती हुई समस्या का कारण बनता है। क्या गलत हो सकता है, गलत हो जाएगा, और जब आप इसकी उम्मीद नहीं करेंगे। इसे मर्फीज लॉ कहा जाता है।

MySQL टीम डेवलपर्स के लिए यह गलती करने के लिए कठिन बनाने के लिए काम कर रही है। 5.7 अनुक्रम में MySQL के नए संस्करणों में एक sql_mode ध्वज है जिसे ONLY_FULL_GROUP_BY कहा जाता है। जब वह ध्वज सेट किया जाता है, तो MySQL सर्वर 1055 त्रुटि देता है और इस तरह की क्वेरी को चलाने से इनकार करता है।

चयन * के साथ ग्रुप मिसिंग, और इसे कैसे ठीक करें।

कभी-कभी एक क्वेरी ऐसा दिखता है, जिसमें SELECT क्लॉज़ में एक * है।

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

ONLY_FULL_GROUP_BY मानक का पालन करने के लिए इस तरह की क्वेरी को वापस लेने की आवश्यकता है।

ऐसा करने के लिए, हमें प्रत्येक item_id लिए number_of_uses मान को वापस करने के लिए GROUP BY का उपयोग करने वाले एक item_id । यह उपशम लघु और मधुर है, क्योंकि इसे केवल uses तालिका को देखने की आवश्यकता है।

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

फिर, हम item तालिका के साथ उस सबक्वेरी में शामिल हो सकते हैं।

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

यह GROUP BY क्लॉज को सरल और सही बनाता है, और हमें * स्पेसियर का उपयोग करने की भी अनुमति देता है।

नोट: फिर भी, बुद्धिमान डेवलपर्स किसी भी मामले में * विनिर्देशक का उपयोग करने से बचते हैं। आमतौर पर उन कॉलमों को सूचीबद्ध करना बेहतर होता है जिन्हें आप क्वेरी में चाहते हैं।

कोई मान()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

item नामक तालिका में पंक्तियों को दिखाता है, संबंधित पंक्तियों की गिनती और संबंधित तालिका में मूल्यों में से एक का uses ।

आप इस ANY_VALUE() फ़ंक्शन को एक अजीब तरह के कुल फ़ंक्शन के रूप में सोच सकते हैं। किसी गणना, राशि या अधिकतम को वापस करने के बजाय, यह MySQL सर्वर को मनमाने ढंग से सवाल में समूह से चुनने के लिए निर्देश देता है। यह एरर 1055 के आसपास काम करने का एक तरीका है।

उत्पादन अनुप्रयोगों में प्रश्नों में ANY_VALUE() का उपयोग करते समय सावधान रहें।

इसे वास्तव में SURPRISE_ME() कहा जाना चाहिए। यह समूह BY समूह में कुछ पंक्ति का मान लौटाता है। यह कौन सी पंक्ति है जो अनिश्चित है। इसका मतलब है कि यह पूरी तरह से MySQL सर्वर तक है। औपचारिक रूप से, यह एक अप्रत्याशित मूल्य देता है।

सर्वर एक यादृच्छिक मूल्य का चयन नहीं करता है, यह उससे भी बदतर है। जब तक आप क्वेरी नहीं चलाते, यह हर बार समान मूल्य देता है। यह बदल सकता है या नहीं, जब कोई तालिका बढ़ती है या सिकुड़ती है, या जब सर्वर में अधिक या कम रैम होती है, या जब सर्वर संस्करण बदलता है, या जब मंगल प्रतिगामी (जो भी अर्थ होता है), या बिना किसी कारण के।

आपको चेतावनी दी गई है।