Другая опция, с которой я просто столкнулся, Silverlight сторона клиента API v0.001 Facebook. Все еще в фазе демонстрации/POC, но довольно многообещающих взглядах.
Я не знаком с хранимыми процедурами. Поэтому я решил их избегать. В конце концов, я сделал это с Python, используя MySQLdb, PHP был бы очень похож:
import MySQLdb
conn = MySQLdb.connect(
host='localhost',
user='root',
passwd='mypassword',
db='mydatabase'
)
c = conn.cursor()
c.execute('SHOW TABLES')
for table in c.fetchall():
c.execute('SELECT * FROM %s' % table[0])
if c.rowcount == 0:
c.execute('DROP TABLE %s' % table[0])
IF (SELECT * FROM tablexy) DROP TABLE tablexy
IF (SELECT * FROM tablexy) DROP TABLE tablexy
(You have to run this queries for each table, because I did not found a way to perform one query on all tables in one query)
It is the same query, but I want to differ from NOTHING and SOMETHING WITH NO CONTENT ;)
This stored procedure should do it:
DELIMITER $$
DROP PROCEDURE IF EXISTS `drop_empty_tables_from` $$
CREATE PROCEDURE `drop_empty_tables_from`(IN schema_target VARCHAR(128))
BEGIN
DECLARE table_list TEXT;
DECLARE total VARCHAR(11);
SELECT
GROUP_CONCAT(`TABLE_NAME`),
COUNT(`TABLE_NAME`)
INTO
table_list,
total
FROM `information_schema`.`TABLES`
WHERE
`TABLE_SCHEMA` = schema_target
AND `TABLE_ROWS` = 0;
IF table_list IS NOT NULL THEN
SET @drop_tables = CONCAT("DROP TABLE ", table_list);
PREPARE stmt FROM @drop_tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
SELECT total AS affected_tables;
END $$
DELIMITER ;
There may be problems with that GROUP_CONCAT
when there are too many empty tables. It depends on the value of the group_concat_max_len
system variable.
It's not possible to do it in one query because DROP TABLE
cannot receive its arguments from a SELECT
query.
Thanks to James for his comments. It appears that the row count query won't return precise results in the case of InnoDB tables, so the above procedure is not guaranteed to work perfectly when there are InnoDB tables in that schema.
For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)
Source: http://dev.mysql.com/doc/refman/5.1/en/tables-table.html
И версию PHP для полноты. Он ничего не уронит, просто напечатайте для вас инструкции DROP:
<?php
$username="root";
$password="mypassword";
$database="mydatabase";
mysql_connect('localhost',$username,$password);
mysql_select_db($database) or die( "Unable to select database");
function drop_empty_tables(){
$tables = mysql_query('SHOW TABLES');
while($table = mysql_fetch_array($tables)){
$table = $table[0];
$records = mysql_query("SELECT * FROM $table");
if(mysql_num_rows($records) == 0){
// mysql_query("DROP TABLE $table");
echo "DROP TABLE $table;\n";
}
}
}
drop_empty_tables();
?>