SQL syntax debug

From LemonWiki共筆
Jump to navigation Jump to search

How to debug MySQL query syntax

Approach 1: Reduce the complexity of Sql query[edit]

Steps:

  • Reduce the complexity of SQL syntax e.g. remove the WHERE condition
  • Examine the query result of subqueries
  • (optional) Enable the MySQL server log contains each query or errors. Details on Testing#Database_server
  • Disable unnecessary permission of database operation

Approach 2: Using MySQL syntax checker[edit]

Approach 3: Beautify the Sql syntax[edit]

Beautify the SQL syntax for better readability.

  • Using the text editor Sublime, and enable the Menu option: View > Syntax >SQL. The syntax will become colorful for easy to read!
  • Instant SQL Formatter

Approach 4: Compare the row counts between source table with target table[edit]

The `source_table` contains raw data. Another `target_table` contains parsed data.

SELECT @timer := CURRENT_TIMESTAMP();
SET @cnt_error := 0;
SET @message := '';

-- un-expected condition
SET @cnt_source := 31;
SET @cnt_target := 25;
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


-- expected condition
SET @cnt_source := 11;
SET @cnt_target := 11;
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


-- real data
SET @cnt_source := (SELECT COUNT(*) FROM `source_table`);
SET @cnt_target := (SELECT COUNT(*) FROM `target_table`);
SELECT IF(@cnt_source <> @cnt_target, @cnt_error := @cnt_error + 1, '');
SELECT IF(@cnt_source <> @cnt_target, @message := CONCAT(@message, 'some problem!', '\r'), '');


SELECT CURRENT_TIMESTAMP() AS 'end time', @timer AS 'start time', TIMEDIFF(CURRENT_TIMESTAMP(), @timer) AS 'time elapsed';
SELECT @cnt_error;
SELECT @message;

Related articles[edit]

Online IDE

MySQL server configuration

Further reading


Troubleshooting of ...

Template