select * from concat("test_",DATE_FORMAT(now(),'%Y%m')) ;
concat("test_",DATE_FORMAT(now(),'%Y%m')) 得到的结果是 test_201107
我想查询test_201107这个表中的数据
如何做select * from concat("test_",DATE_FORMAT(now(),'%Y%m')) ;这句话是报错的求高手指点
concat("test_",DATE_FORMAT(now(),'%Y%m')) 得到的结果是 test_201107
我想查询test_201107这个表中的数据
如何做select * from concat("test_",DATE_FORMAT(now(),'%Y%m')) ;这句话是报错的求高手指点
+-------------------------------------------+
| concat("test_",DATE_FORMAT(now(),'%Y%m')) |
+-------------------------------------------+
| test_201107 |
+-------------------------------------------+
1 row in set (0.03 sec)
PREPARE STML FROM @ASQL;
EXECUTE STML;
mysql help
PREPARE stmt_name FROM preparable_stmt The PREPARE statement prepares a statement and assigns it a name, stmt_name, by which to refer to the statement later. Statement names are not case sensitive. preparable_stmt is either a string literal or a user variable that contains the text of the statement. The text must represent a single SQL statement, not multiple statements. Within the statement, ‘?’ characters can be used as parameter ers to indicate where data values are to be bound to the query later when you execute it. The ‘?’ characters should not be enclosed within quotes, even if you intend to bind them to string values. Parameter ers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth. If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared. This means that if the new statement contains an error and cannot be prepared, an error is returned and no statement with the given name exists. The scope of a prepared statement is the client session within which it is created. Other clients cannot see it. EXECUTE stmt_name [USING @var_name [, @var_name] ...] After preparing a statement, you execute it with an EXECUTE statement that refers to the prepared statement name. If the prepared statement contains any parameter ers, you must supply a USING clause that lists user variables containing the values to be bound to the parameters. Parameter values can be supplied only by user variables, and the USING clause must name exactly as many variables as the number of parameter ers in the statement. You can execute a given prepared statement multiple times, passing different variables to it or setting the variables to different values before each execution.
我执行的时候有些问题生成的sql语句是这样的insert into test select * from test_201107;
单独拿出来就可以
但是用PREPARE STML FROM @ASQL;
就不行
我的代码就是想执行 insert into test select * from test_201107;但是我去PREPARE STML FROM @ASQL;执行是不成功的 我select @ASQL看到那个sql语句拿出来执行是可以的是不是不能用insert啊
set @ASQL = CONCAT("INSERT INTO tmp( user_id, num ,`type`,`from`)
SELECT user_id, count( * ) AS num ,method,'",concat('lomo_track_',
DATE_FORMAT(now(),'%Y%m')) ,
"' from track_",
DATE_FORMAT(now(),'%Y%m'),
" WHERE method in ('photo','comment','likes','followers','followings') and user_id > 0 GROUP BY user_id
");PREPARE STML FROM @ASQL;
SELECT user_id, count(*) AS num ,method,`",CONCAT('lomo_track_',
DATE_FORMAT(NOW(),'%Y%m')),'` from track_',DATE_FORMAT(NOW(),'%Y%m'),
" WHERE method in ('photo','comment','likes','followers','followings') and user_id > 0 GROUP BY user_id
");
mysql> PREPARE STML FROM @ASQL;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
SELECT user_id, count(*) AS num ,method,`",CONCAT('lomo_track_',
DATE_FORMAT(NOW(),'%Y%m')),'` from track_',DATE_FORMAT(NOW(),'%Y%m'),
" WHERE method in ('photo','comment','likes','followers','followings') and user_id > 0 GROUP BY user_id
");
PREPARE STML FROM @ASQL;
execute stml;