比较两个选择查询结果的相同数据mysql

i'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query.

"Correct answer" query is in database, and the comparable select is inputed by user.

i readed about EXCEPT and INTERSECT but they don't exist in mysql.

example i have this database structure: db pic

Question from the application:

  1. List the first name, last name and date of birth of all female students whose first name is Yana but their last name is not Taylor.

correct answer-

SELECT FIRSTNAME, LASTNAME, DATEOFBIRTH 
FROM PERSON 
JOIN STUDENTS ON PERSONID=STUDENTID
WHERE GENDER=’F’ AND FIRSTNAME=’YANA’ AND LASTNAME<>’TAYLOR’

i want to check that the returned result is identical. wrong answer will be mysql query for example with aliased columns:

SELECT FIRSTNAME as first_name, LASTNAME as last_name, DATEOFBIRTH as date_of_birth ....

or even like statement (if it returns more results):

WHERE GENDER=’F’ AND FIRSTNAME LIKE '%YANA%' AND LASTNAME<>'TAYLOR'

so my question is how to compare results between two queries that:

  • their returned row count is the same
  • their column names are equal
  • its from the same table
  • and maybe return some indication for true or false that they are identical?

The best approach, in my opinion, is to run both queries (the stored query, and the typed query), and then read both results to separate arrays, and analyze the result:

  • a count() in each array will tell if number of rows are equal or not.
  • if you put together your arrays using returned field names, it will be easy to see if they are the same.
  • you don't even need to worry if the table is correct, if both resultsets match.
  • you can use PHP array manipulation functions to compare the rows in any way you may need. A simple if($array1 === $array2) will tell you if both arrays have identical keys and values.

If you don't need to analyze SQL statement formatting, and only needs to know if the query runs fine and brings the correct data, then this method will be enough.

This might be a little bit greedy but you could create a MD5 for each record and then calculate a MD5 for all these MD5. Compare the two values and if they are correct then the result is the same.

For example

SELECT md5(GROUP_CONCAT(a.md)) FROM (
    SELECT 
      first_name, LASTNAME,
      md5(concat(first_name, LASTNAME)) as md from PERSON
    ) as a;