SQL syntax debug: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
 
(17 intermediate revisions by the same user not shown)
Line 1: Line 1:
How to debug MySQL query syntax
How to debug MySQL query syntax


Goal:  
== Approach 1: Reduce the complexity of Sql query ==
* Reduce the complexity


Steps:
Steps:
* Reduce the complexity of SQL syntax ex: reduce the {{ kbd | key = WHERE }} condition
* Reduce the complexity of SQL syntax e.g. remove the {{ kbd | key = WHERE }} condition
* Examine the sub query part only
* Examine the query result of subqueries
* Enable the MySQL server log. Details on [[Testing#Database_server]]
* (optional) Enable the MySQL server log contains each query or errors. Details on [[Testing#Database_server]]
* Disable unnecessary database operation permission
* Disable unnecessary permission of database operation
* Beautify the sql syntax e.g.
 
** Using the text editor [http://www.sublimetext.com/ Sublime], and enable the Menu option: {{ kbd | key = View > Syntax >SQL }}. The syntax will become colorful for easy to read!
== Approach 2: Using MySQL syntax checker ==
** [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]
* {{Gd}} [https://www.mysql.com/products/workbench/ MySQL :: MySQL Workbench] (blog: [http://mysqlworkbench.org/ The MySQL Workbench Developer Central Site]): Syntax checker for MySQL statements. Required to install the software.
* {{Gd}} [http://developer.mimer.se/validator/index.htm Mimer SQL Developers - Mimer SQL Validator] Syntax checker for SQL-2003 standard and so on. Not required to install the software, checking result was able to be viewed using the browser.
* [http://cweiske.de/php-sqllint.htm cweiske.de: tools: PHP-SQLlint]: Required to install the software.
* More on [https://www.quora.com/Where-can-I-find-a-good-MySQL-syntax-checker Where can I find a good MySQL syntax checker? - Quora]
 
== Approach 3: Beautify the Sql syntax ==
Beautify the SQL syntax for better readability.
* Using the text editor [http://www.sublimetext.com/ Sublime], and enable the Menu option: {{ kbd | key = View > Syntax >SQL }}. The syntax will become colorful for easy to read!
* [http://www.dpriver.com/pp/sqlformat.htm Instant SQL Formatter]
 
== Approach 4: Compare the row counts between source table with target table ==
The {{kbd | key=<nowiki>`source_table`</nowiki>}} contains raw data. Another {{kbd | key=<nowiki>`target_table`</nowiki>}} contains parsed data.
<pre>
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;
</pre>
 
== Related articles ==
Online IDE
* {{Gd}} [https://sqliteonline.com/ SQL OnLine IDE]


MySQL server configuration
MySQL server configuration
* [http://mysqltuner.com/ MySQLTuner-perl by major]
* [http://mysqltuner.com/ MySQLTuner-perl by major]
* [[Troubleshooting of MySQLTuner]]


Further reading
Further reading
* Craig Kerstiens (2016). [http://www.craigkerstiens.com/2016/01/08/writing-better-sql/ Writing more legible SQL] {{access | date = 2016-11-03}}
* Craig Kerstiens (2016). [http://www.craigkerstiens.com/2016/01/08/writing-better-sql/ Writing more legible SQL] {{access | date = 2016-11-03}}
{{Template:Troubleshooting}}


[[Category:Programming]] [[Category:MySQL]] [[Category:Data Science]]
[[Category:Programming]] [[Category:MySQL]] [[Category:Data Science]]

Latest revision as of 11:39, 7 September 2020

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

Retrieved from "https://wiki.planetoid.info/index.php?title=SQL_syntax_debug&oldid=22041"