开发者

In mysql, how to avoid selection if a table exists

Let's say I run the following query:

CREATE TEMPORARY TABLE IF NOT EXISTS FISH SELECT 'a';

it will create a temporary table with 'a' in it. Now, if I run the query again, the tab开发者_如何学Pythonle will not be recreated, but the select will happen again - so, the table will have 'a' twice in it. How do I prevent this from happening, i.e. if the table exists, no action should be carried out?


This behavior is explicitly documented in the manual, quote:

For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows:

  • The table definition given in the CREATE TABLE part is ignored. No error occurs, even if the definition does not match that of the existing table.

  • If there is a mismatch between the number of columns in the table and the number of columns produced by the SELECT part, the selected values are assigned to the rightmost columns. For example, if the table contains n columns and the SELECT produces m columns, where m < n, the selected values are assigned to the m rightmost columns in the table. Each of the initial n – m columns is assigned its default value, either that specified explicitly in the column definition or the implicit column data type default if the definition contains no default.

  • If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error.

and there is no mention of any method to change this behavior so I suppose your only solution is to first check for table's existence with a separate query then CREATE...SELECT the table if it doesn't exist.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜