Mysql> USE test;
Mysql> The CREATE TABLE t1 (a INT the NOT NULL);
Mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

Mysql> The SET @ table='t1';
Mysql> SET @ s=CONCAT (' SELECT * FROM '@ table).

Mysql> PREPARE stmt3 FROM @ s;
Mysql> The EXECUTE stmt3;
+ - +
| a |
+ - +
4 | |
8 | |
11 | |
32 | |
80 | |
+ - +

Mysql> DEALLOCATE PREPARE stmt3;





This is simply a mysql prepare grammar test statements, I have a question that is
The following SQL statements can be used as a preliminary statement:


The ALTER TABLE
The ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE the MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{the CREATE | DROP} INDEX
{the CREATE | RENAME | DROP} DATABASE
{the CREATE | DROP} TABLE
{the CREATE | RENAME | DROP} USER
{the CREATE | DROP} VIEW
The DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
| LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT,
INSERT
INSTALL the PLUGIN
KILL
The LOAD INDEX INTO the CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR the TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW the CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
LOGS SHOW {MASTER | BINARY}
The STATUS SHOW {MASTER | SLAVE}
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
The UPDATE


These statements are in which statement
PREPARE statement

CodePudding user response:

Impression as long as it is a single statement can be PREPARE, LZ these probably refers to these statements can be PREPARE to quote

CodePudding user response:

Can't find the instance, there is no way of thinking to solve this problem

CodePudding user response:

refer to the second floor qq_37311616 response:

cannot find instances, no ideas to solve this problem

You stick syntax is said above, these statements can be as prepare statement,

CodePudding user response:

Create a stored procedure, enter the name of the two tables to $tablename1 $$tablename2 and an integer n, the former $n line of the first table records are copied to the second in the table, and the actual copy number of rows returned by a variable, assuming that the table doesn't exist, return the number of rows to 1,
Drop procedure if the exists sp8;
Delimiter $$
Create procedure sp8 (
$tablename1 varchar (255),
$tablename2 varchar (255),
$n int,
The OUT $rowcount int
)
The begin
/* tables in mysql system to judge the existence of the source data table and if there is a return 1, */
If not the exists (select * from INFORMATION_SCHEMA. TABLES where
TABLE_SCHEMA='mySales and TABLE_NAME=$tablename1) then
The set $rowcount=1;
The else
/* to retrieve the total number of rows in the table, can't use in prepared statements? As the parameters of the table name, */
The set @ SQL=concat (' SELECT count (*) into @ total FROM ', $tablename1);
Prepare STMT FROM @ SQL;
The execute STMT.
If ($n> @ total) then set $n=@ total; end if; # can determine the actual copy number of rows
/* delete table 2, */
SQL=set @ concat (' drop table if exists, $tablename2);
Prepare STMT FROM @ SQL;
The execute STMT.
/* bulk copy data to the table 2, table 1 */
Set @ SQL=concat (' create table '$tablename2,' the select * from '$tablename1,' limit ', $n);
@ # the SELECT SQL;
Prepare STMT FROM @ SQL;
The execute STMT.
Deallocate prepare STMT.
The set $rowcount=$n;
end if;
End $$
Delimiter.