Problem using IF EXISTS

Question

Problem using IF EXISTS

Navigation

#1 by (1 votes)

1

I'm new to this, I'm having trouble implementing the following code within a stored procedure:

Basically, what I'm looking for is to check if the table exists and if it exists, delete the data and then the table:

IF EXISTS (select TABLE_NAME from information_schema.tables where TABLE_name='WEB_SESSION_PH') 
    begin
      truncate table WEB_SESSION_PH
      drop table WEB_SESSION_PH
    end

But I get the error:

Error Code: 1064. 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 'IF EXISTS (select TABLE_NAME from information_schema.tables where TABLE_name = 'WE' at line 1 0.000 sec

Any suggestions? Thanks

mysql

asked by ekom77 27.11.2017 в 11:51

source

1 answer

Show a specific data of an associative array Get res.locals values in another midleware

score 1 · Accepted Answer

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. You must have the privilege DROP for each table.

Be careful with this affirmation! Remove the table definition and all data from the table. For a partitioned table, it permanently deletes the table definition, all its partitions, and all the data stored in those partitions. It also removes the partition definitions associated with the discarded table.

NOTE

As of MySQL 5.7.6, the partition definition files   (.par) are no longer created for partitioned InnoDB tables. In contrast,   partition definitions are stored in the internal dictionary of   InnoDB data. The partition definition files (.par) are   still used for partitioned MyISAM tables.

DROP TABLE causes implicit confirmation, except when used with the TEMPORARY keyword. See the Section 13.3.3, "Statements that cause an implied commitment" .

Important

When a table is discarded, the privileges granted   specifically for the table they are not automatically deleted. They must   abandon yourself manually See Section 13.7.1.4, "GRANT Syntax" .

If there are no tables named in the argument list, the instruction returns an error that indicates by name which nonexistent tables could not be deleted, but also discards all the tables in the list that do exist.

Use IF EXISTS to avoid an error occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table; These notes can be viewed with SHOW WARNINGS . See Section 13.7.5.40, "SHOW WARNING Syntax" .

p>

IF EXISTS can also be useful for deleting tables in unusual circumstances where there is a .frm file but no table managed by the storage engine. (For example, if an abnormal server exit occurs after removing the table from the storage engine but before deleting the .frm file).

The TEMPORARY keyword has the following effects:

The instruction only includes TEMPORARY tables.
The declaration does not cause an implicit confirmation.
Access rights are not verified. A TEMPORARY table is only visible with the session that created it, so it is not necessary verification.

The use of TEMPORARY is a good way to make sure you do not accidentally fall into a table other than TEMPORARY.

The RESTRICT and CASCADE keywords do nothing. They are allowed to facilitate the transfer from other database systems.

DROP TABLE does not support all configurations innodb_force_recovery . See the Section 14.21.2, "How to Force InnoDB Recovery" .