mysql query syntax error，LIMIT can't used before UNION any more after mysql8.0？
select * from t_google_receipt limit 1 union select * from t_old_google_receipt limit 1；
the sql in my code excuted sucessful in my old mysql(Server version: 5.6.17-log MySQL Community Server (GPL)),but after updated to mysqlGroup(Server version: 8.0.11 MySQL Community Server - GPL), it excuted failed,
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'union select * from t_old_google_receipt limit 1' at line 1;
It actually changed in MySQL 5.7:
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.
Beware anyway that if you don't provide individual
ORDER BY clauses you'll get arbitrary rows (not even random).
MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement, A SELECT statement can start with a WITH clause to define common table Use of an unqualified * with other items in the select list may produce a parse error. PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. In addition to the above syntax, MySQL provides the following alternative LIMIT clause for compatibility with PostgreSQL. LIMIT row_count OFFSET offset LIMIT and ORDER BY clauses. The SELECT statement without an ORDER BY clause returns rows in an unspecified order. It means that rows can be in any order.
MySQL Restrictions and Limitations :: 5 Restrictions on Subqueries, MySQL does not support LIMIT in subqueries for certain subquery operators: BY s1 LIMIT 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT For example, if f() inserts rows, the following query can modify data: The SQL syntax includes a number of functions that allow you to interpret or run calculations on your data just by issuing a SELECT query. These are known as aggregate functions . The COUNT function counts and returns the number of rows that match a certain criteria.
For union and union all you have to consider below point
- Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order
So in case of two tables union it is bad practice to use select all(select *) column from table rather it is preferable and less error to use column name explicitly like below
select col1,col2... coln from t1 union select col1,col2.....coln from t2
MySQL 8.0 Reference Manual :: 8.4.7 Limits on Table , For example, InnoDB has a limit of 1017 columns per table. ENGINE=MyISAM CHARACTER SET latin1; ERROR 1118 (42000): Row size too large. g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1; Query OK, 0 rows affected two bytes to store the length of the value, so each value can take up to 767 bytes. MySQL - Select Query - The SQL SELECT command is used to fetch data from the MySQL database. You can use this command at mysql> prompt as well as in any script like PHP.
MySQL LIMIT, When you apply the LIMIT clause to this unordered result set, you will not know which rows the query will return. For example, you may want to get fifth through Here's a parameterised query function for MySQL similar to pg_query_params, I've been using something similar for a while now and while there is a slight drop in speed, it's far better than making a mistake escaping the parameters of your query and allowing an SQL injection attack on your server.
PHP, Syntax: SELECT column1, column2, FROM table_name LIMIT offset, count;. You can learn about LIMIT clause in details in article MySQL | LIMIT Clause. The syntax for creating a SQL comment in MySQL using # symbol is: # comment goes here. In MySQL, a comment started with # symbol must be at the end of a line in your SQL statement with a line break after it. This method of commenting can only span a single line within your SQL and must be at the end of the line. Syntax Using --symbol. The
MySQL: SELECT LIMIT Statement, The MySQL SELECT LIMIT statement is used to retrieve records from one or more tables in MySQL and limit the You can learn more about how we use advertisements here. Donate The syntax for the SELECT LIMIT statement in MySQL is: This MySQL tutorial explains how to use the AND condition and the OR condition together in a MySQL query with syntax and examples. The MySQL AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement.