清除数据库内所有表的数据【MySQL】 {#清除数据库内所有表的数据mysql}

-- 如果存在，则删除存储过程
drop procedure IF EXISTS delAllTableData;
delimiter $$
-- 删除数据库所有数据
create procedure delAllTableData(tableName varchar(255))
begin
    declare i int default 0;
    declare table_size int default 0;
    declare table_name_param varchar(255);
    SELECT count(*) into table_size FROM information_schema.tables WHERE table_schema = tableName;

    while (i <span class="hljs-operator">&lt;</span> table_size) do
        <span class="hljs-keyword">SELECT</span> TABLE_NAME <span class="hljs-keyword">into</span> table_name_param <span class="hljs-keyword">FROM</span> information_schema.tables <span class="hljs-keyword">WHERE</span> table_schema <span class="hljs-operator">=</span> tableName limit <span class="hljs-number">1</span> <span class="hljs-keyword">offset</span> i;
        <span class="hljs-keyword">set</span> i <span class="hljs-operator">=</span> i<span class="hljs-operator">+</span><span class="hljs-number">1</span>;
        <span class="hljs-keyword">set</span> <span class="hljs-variable">@tem_table</span> :<span class="hljs-operator">=</span> concat(<span class="hljs-string">'truncate table '</span>,table_name_param);
        <span class="hljs-keyword">prepare</span> tem_table <span class="hljs-keyword">from</span> <span class="hljs-variable">@tem_table</span>;
        <span class="hljs-keyword">execute</span> tem_table;
    <span class="hljs-keyword">end</span> while;



end` $$;
delimiter ;
`-- 引号内是需要清除数据的数据库名称`
`call` delAllTableData(`'database_name'`);
`