AM sử dụng MySQL 5.7.13 trên PC chạy windows của tôi với Máy chủ WAMP

Đây là vấn đề của tôi khi thực hiện truy vấn này

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

Tôi luôn nhận được lỗi như thế này

Biểu thức số 1 của danh sách SELECT không nằm trong mệnh đề GROUP BY và chứa cột không được tổng hợp 'returnntr_prod.tbl_customer_pod_uploads.id' không phụ thuộc chức năng vào các cột trong mệnh đề GROUP BY; điều này không tương thích với sql_mode = only_full_group_by

Bạn có thể vui lòng cho tôi biết giải pháp tốt nhất ...

Tôi cần Kết quả như

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name    | is_private | status | createdon           | updatedon           |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
|  1 |       1 | 78      | 1       | 21.1212  | 21.5454   |          1 |             1 | id_Card.docx |          0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

Điều này

Biểu thức số 1 của danh sách SELECT không nằm trong mệnh đề GROUP BY và chứa cột không được tổng hợp 'returnntr_prod.tbl_customer_pod_uploads.id' không phụ thuộc chức năng vào các cột trong mệnh đề GROUP BY; điều này không tương thích với sql_mode = only_full_group_by

sẽ được giải quyết đơn giản bằng cách thay đổi chế độ sql trong MySQL bằng lệnh này,

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Điều này cũng phù hợp với tôi .. Tôi đã sử dụng cái này, vì trong dự án của tôi có nhiều Truy vấn như vậy nên tôi chỉ thay đổi chế độ sql này thành only_full_group_by

Cảm ơn bạn... :-)

Khi only_full_group_bychế độ của MySQL được bật, có nghĩa là các quy tắc ANSI SQL nghiêm ngặt sẽ được áp dụng khi sử dụng GROUP BY. Đối với truy vấn của bạn, điều này có nghĩa là nếu bạn GROUP BYthuộc proof_typecột, thì bạn chỉ có thể chọn hai điều:

• các proof_typecột, hoặc
• tổng hợp của bất kỳ cột nào khác

Bằng cách "uẩn" của các cột khác, tôi có nghĩa là sử dụng một chức năng tổng hợp như MIN(), MAX()hoặc AVG()với cột khác. Vì vậy, trong trường hợp của bạn, truy vấn sau sẽ hợp lệ:

SELECT proof_type,
       MAX(id) AS max_id,
       MAX(some_col),
       MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
      status = 'Active'
GROUP BY proof_type

Phần lớn các GROUP BYcâu hỏi MySQL mà tôi thấy trên SO đều bị tắt chế độ nghiêm ngặt, vì vậy truy vấn đang chạy nhưng với kết quả không chính xác. Trong trường hợp của bạn, truy vấn hoàn toàn không chạy, buộc bạn phải suy nghĩ về những gì bạn thực sự muốn làm.

Lưu ý: ANSI SQL mở rộng những gì được phép chọn GROUP BYbằng cách bao gồm cả các cột phụ thuộc về mặt chức năng vào (các) cột đang được chọn. Một ví dụ về phụ thuộc hàm sẽ được nhóm theo cột khóa chính trong bảng. Vì khóa chính được đảm bảo là duy nhất cho mọi bản ghi, do đó giá trị của bất kỳ cột nào khác cũng sẽ được xác định. MySQL là một trong những cơ sở dữ liệu cho phép điều này (SQL Server và Oracle không AFAIK).

Sử dụng bất kỳ một giải pháp nào

(1) PHPMyAdmin

nếu bạn đang sử dụng phpMyAdmin thì hãy thay đổi cài đặt " sql_mode " như được đề cập trong ảnh chụp màn hình bên dưới.

Chỉnh sửa biến "chế độ sql" và xóa văn bản "ONLY_FULL_GROUP_BY" khỏi giá trị

(2) SQL / Command prompt Chạy lệnh dưới đây.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

(3) Không sử dụng SELECT *

Sử dụng cột có liên quan trong truy vấn CHỌN. các cột có liên quan, nằm trong mệnh đề "nhóm theo" hoặc cột có hàm tổng hợp (MAX, MIN, SUM, COUNT, v.v.)

Lưu ý quan trọng

Các thay đổi được thực hiện bằng cách sử dụng điểm (1) HOẶC điểm (2) sẽ không đặt nó VĨNH VIỄN, và nó sẽ hoàn nguyên sau mỗi lần khởi động lại.

Vì vậy, bạn nên đặt điều này trong tệp cấu hình của mình (ví dụ: /etc/mysql/my.cnf trong phần [mysqld]), để các thay đổi vẫn có hiệu lực sau khi khởi động lại MySQL:

Tệp cấu hình : /etc/mysql/my.cnf

Tên biến : sql_mode HOẶC sql-mode

Phương pháp dưới đây đã giải quyết vấn đề của tôi:

Trong ubuntu

Kiểu: sudo vi /etc/mysql/my.cnf

gõ A để vào chế độ chèn

Trong dòng cuối cùng, hãy dán hai mã dòng bên dưới:

[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

Gõ esc để thoát khỏi chế độ đầu vào

Type :wq to save and close vim.

Nhập sudo service mysql restartđể khởi động lại MySQL.

Bạn có thể vô hiệu hóa sql_mode = only_full_group_by bằng một số lệnh, bạn có thể thử điều này bằng terminal hoặc MySql IDE

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';

Trong Ubuntu

Bước 1:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Bước 2: Chuyển đến dòng cuối cùng và thêm phần sau

sql_mode = ""

Bước 3: Lưu

Bước 4: Khởi động lại máy chủ mysql.

Để truy vấn hợp pháp trong SQL92, cột tên phải được bỏ qua khỏi danh sách chọn hoặc có tên trong mệnh đề GROUP BY.

SQL99 trở lên cho phép các tập hợp như vậy cho mỗi tính năng tùy chọn T301 nếu chúng phụ thuộc về mặt chức năng vào các cột GROUP BY: Nếu mối quan hệ như vậy tồn tại giữa tên và custid, truy vấn là hợp pháp. Đây sẽ là trường hợp, ví dụ, custid là khóa chính của khách hàng.

MySQL 5.7.5 trở lên thực hiện phát hiện sự phụ thuộc chức năng. Nếu chế độ SQL ONLY_FULL_GROUP_BY được bật (theo mặc định), MySQL sẽ từ chối các truy vấn mà danh sách chọn, điều kiện HAVING hoặc danh sách ORDER BY đề cập đến các cột không được tổng hợp không có tên trong mệnh đề GROUP BY cũng như không phụ thuộc vào chúng .

thông qua MySQL :: MySQL 5.7 Hướng dẫn tham khảo :: 12.19.3 Xử lý MySQL của GROUP BY

Bạn có thể giải quyết nó bằng cách thay đổi chế độ sql với lệnh này:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

và ... nhớ kết nối lại cơ sở dữ liệu !!!

đi tới phpmyadmin và mở bảng điều khiển và thực hiện yêu cầu này

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Xin chào thay vì lấy tất cả các cột, chỉ cần lấy những gì bạn cần bằng cách sử dụng ANY_VALUE(column_name). Nó đang hoạt động hoàn hảo. Chỉ kiểm tra thôi.

Ví dụ:

SELECT proof_type,any_value("customer_name") as customer_name
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type`

Tìm kiếm "Chế độ SQL" nếu bạn đang sử dụng PhpMyAdmin và loại bỏ giá trị : ONLY_FULL_GROUP_BY, vừa làm và không sao.

> sudo nano /etc/mysql/my.cnf

Nhập bên dưới

[mysqld]
sql_mode = ""

Ctrl + O => Y = Ctrl + X

> sudo service mysql restart

Từ cách nó trông, tôi nghĩ rằng việc nhóm theo nhiều cột / trường sẽ không làm ảnh hưởng đến kết quả của bạn. Tại sao bạn không thử thêm vào nhóm bằng cách như thế này:

GROUP BY `proof_type`, `id`

Điều này sẽ nhóm theo proof_typeđầu tiên sau đó id. Tôi hy vọng điều này không làm thay đổi kết quả. Trong một số / hầu hết các trường hợp, nhóm theo nhiều cột cho kết quả sai.

1. Đăng nhập phpMyAdmin
2. Điều hướng đến: Máy chủ: localhost: 3306 và không chọn bất kỳ cơ sở dữ liệu nào
3. Nhấp vào các biến từ menu trên cùng
4. Tìm kiếm "chế độ sql" và chỉnh sửa giá trị tương ứng thành: NO_ENGINE_SUBSTITUTION

Đó là tất cả.

Tôi đã làm điều này trong Ec2 của mình và nó hoạt động như một sự quyến rũ.

Đây là một cách thực sự nhanh chóng và dễ dàng để thiết lập nó vĩnh viễn

NB: đang chạy SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); tạm thời và khi khởi động lại máy chủ, bạn vẫn sẽ gặp lỗi. Để khắc phục điều này vĩnh viễn, hãy làm như dưới đây

1. Đăng nhập vào máy chủ của bạn với tư cách là người chủ
2. trong quá trình chạy thiết bị đầu cuối của bạn wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
3. Làm cho tập lệnh có thể thực thi bằng cách chạy chmod +x disable-strict-mode.sh
4. Chạy script bằng cách chạy ./disable-strict-mode.sh

Và bạn đã hoàn tất, các thay đổi sẽ được thực hiện đối với mysql và nó sẽ được khởi động lại

Cập nhật cho MySQL 8.0

Của bạn sql-modesẽ không có NO_AUTO_CREATE_USERvì nó đã bị xóa như đã đề cập ở đây - how-to-set-sql-mode-in-my-cnf-in-mysql-8

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Ngoài ra, nếu ai đó không có my.cnftệp thì họ tạo một tệp mới trong /etc/my.cnfvà sau đó thêm các dòng trên.

Tôi đã gặp vấn đề tương tự với một phần của việc nghiền nát cơ sở dữ liệu của mình. Những gì tôi đã làm là tôi đã thay đổi tham số trong DB thông qua bảng điều khiển cơ sở dữ liệu PHPStorm như sau:

   SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

hoạt động như sự quyến rũ bây giờ

Chỉ tắt only_full_group_bynếu bạn biết mình đang làm gì!

Một ví dụ đơn giản để chứng minh vấn đề:

Bảng: người dùng

 id   |  name
----------------
  1      ali
  2      john
  3      ali

Khi bạn sử dụng GROUP BYtrên namecột:

SELECT * FROM users GROUP BY name;

Có hai kết quả có thể xảy ra:

  1      ali
  2      john     

HOẶC LÀ

  2      john
  3      ali

MYSQL không biết phải chọn kết quả nào! Bởi vì có những ids khác nhau nhưng cả hai đều có name=ali.

Giải pháp 1:

chỉ chọn nametrường:

CHỌN tên TỪ người dùng NHÓM THEO tên;

kết quả:

  ali
  john     

Giải pháp 2:

Đang tắt only_full_group_by. MYSQL sẽ cho bạn thấy một trong hai kết quả khả thi mà tôi đã hiển thị NGẪU NHIÊN trước đó !! (Sẽ ổn nếu bạn không thực sự quan tâm idnó sẽ chọn gì)

Giải pháp3

Sử dụng một Aggregatechức năng như MIN(), MAX()để giúp MYSQL quyết định những gì nó phải chọn.

ví dụ tôi muốn mức tối thiểu id:

SELECT MIN(id), name FROM users GROUP BY name;

kết quả:

  1      ali
  2      john     

Nó sẽ chọn alihàng có giá trị tối thiểu id.