| |
SQL syntax parser is a very powerful built-in toolset, which will involve a lot of compilation principles related knowledge, grammar analysis, lexical analysis. . A lot of seemingly theoretical stuff, but it looks boring, their value is more obvious.
If you look at the history, you will find a lot of people are known as the master of the field of programming are the master of writing the first microcomputer to run the Basic language of Bill Gates, the design Delphi's Borland's "the world's most powerful programmer," Sun's father of JAVA, Bell Labs C + + father
The starting point to mention some high, and today to share with you is a very simple sql statement in MySQL to resolve the problem, but in Oracle can be successfully resolved through this one can also see some of the details of Oracle and MySQL In some of the differences.
Need to run the statement is as follows:
SELECT THREAD_ID, threads.NAME, SUM (COUNT_STAR) AS Totalcount, SUM (SUM_TIMER_WAIT) AS Totaltime
FROM performance_schema.events_waits_summary_by_thread_by_event_name
INNER JOIN performance_schema.threads USING (THREAD_ID)
WHERE threads.NAME LIKE 'thread / sql / slave \ -%'
GROUP BY THREAD_ID, threads.NAME;
ERROR 1630 (42000): FUNCTION performance.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
At first glance this error, the feeling must be where a problem, they find a clear statement for a while did not find the problem, but from the error point of view seems to occur in the words near the sum.
Simply put the first line in the
SUM (COUNT_STAR) AS Totalcount to SUM (COUNT_STAR) AS Totalcount problem solved on the cutting edge. Can be seen that the problem is a very small problem, strictly speaking, the statement is not written rigorously. But in my impression Oracle seems to be on this bell situation is Shoudaoqinlai, the impression has not been such a problem.
Let's take a quick look at this issue in MySQL and Oracle to see what happens.
In MySQL
Create table parse_test (id int, name varchar (30));
Insert into parse_test values (1, 'aa');
Insert into parse_test values (2, 'bb');
Commit;
Mysql> select count (id) from parse_test;
+ ----------- +
| Count (id) |
+ ----------- +
| 2 |
+ ----------- +
1 row in set (0.00 sec)
Mysql> select count (id) from parse_test;
ERROR 1630 (42000): FUNCTION test.count does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual
In Oracle, there is no problem.
Create table parse_test (id number, name varchar2 (30));
Insert into parse_test values (1, 'aa');
Insert into parse_test values (2, 'bb');
Commit;
Select count (id) from parse_test;
COUNT (ID)
----------
2
Select count (id) from parse_test;
COUNT (ID)
----------
2
Oracle analytic complex sql statement aside the advantages of Oracle can be seen in this detail does support the intensity to be higher.
But the beginning of the MySQL error in the statement into the Oracle inside (if the table structure data are assumed to exist), it must not run. The reason is that the definition of the maximum length of Oracle is 30, but in MySQL to support a lot more, up to 64 bits.
Mysql> create table events_waits_summary_by_thread_by_event_name567890123456789012345 (id int, name varchar (30));
ERROR 1059 (42000): Identifier name 'events_waits_summary_by_thread_by_event_name567890123456789012345' is too long
Mysql> select length ( 'events_waits_summary_by_thread_by_event_name567890123456789012345');
+ ------------------------------------------------- ---------------------------- +
| Length ( 'events_waits_summary_by_thread_by_event_name567890123456789012345') |
+ ------------------------------------------------- ---------------------------- +
| 65 |
+ ------------------------------------------------- ---------------------------- +
1 row in set (0.01 sec)
Of course, I am also so far I am also picking up a small stone, get some water for the sql parser more in-depth questions, if careful study, from the MySQL code level to analyze the problem to a basic goal. |
|
|
|