标签： MySQL

模糊计数

select table_name,table_rows from information_schema.tables 
where TABLE_SCHEMA = '<datebase_name>' 
order by table_rows desc;

精确计数

1.执行以下语句

select concat(
'select "',
table_name,
'", count(*) from ',
TABLE_SCHEMA,
'.',
table_name,
' union all'
) from information_schema.tables
where TABLE_SCHEMA='<datebase_name>';

2.复制第1步语句的查询结果，粘贴之后将最后一个union all替换为;

select "<table1_name>", count(*) from <datebase_name>.<table1_name> union all
select "<table2_name>", count(*) from <datebase_name>.<table2_name> union all
...
select "<tablen_name>", count(*) from <datebase_name>.<tablen_name>;

3. 复制第2步语句的查询结果，然后运行，查询结果如下：

+-------------+-------------+
| <table1_name>   | count(*)                 |
+-------------+-------------+
| <table1_name>   |      <table1_rows> |
| <table2_name>   |      <table2_rows> |
| ...                   | ...                      |
| <tablen_name>   |      <tablen_rows> |
+--------------------------+----------+

* 拓展：直接统计总数

select sum(sum1) from (
select count(*) as sum1 from <datebase_name>.<table1_name> union all
select count(*) as sum1 from <datebase_name>.<table2_name> union all
...
select count(*) as sum1 from <datebase_name>.<tablen_name>) as total;

查询结果如下：

+--------------------------+
| sum(sum1)        |
+--------------------------+
|<total_rows>|
+--------------------------+

© Sunplace,2020 5月29日，当我打开本地数据库的时候，除一个数据库没有问题外，其余的数据库内的表已经被清空，只有一张warning表和里面的内容。另外新建了一个please_read_me_vvv的数据库。

mysql> use please_read_me_vvv
Database changed
mysql> show tables;
+------------------------------+
| Tables_in_please_read_me_vvv |
+------------------------------+
| warning |
+------------------------------+
1 row in set (0.01 sec)

mysql> select * from warning;
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+--------------------------------+
| id | warning | Bitcoin_Address | Email |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+--------------------------------+
| 1 | To recover your lost Database and avoid leaking it: Send us 0.03 Bitcoin (BTC) to our Bitcoin address 1GkZpdfQdUQasnt12P9pSnx8sohm4NgqNQ and contact us by Email with your Server IP or Domain name and a Proof of Payment. If you are unsure if we have your data, contact us and we will send you a proof. Your Database is downloaded and backed up on our servers. Backups that we have right now: bbs, co_cms, oa, wordpress. If we dont receive your payment in the next 5 Days, we will make your database public or use them otherwise. | 1GkZpdfQdUQasnt12P9pSnx8sohm4NgqNQ | [email protected] |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+--------------------------------+
1 row in set (0.00 sec)

不过，相对“重要”的数据库没有受到影响。 事发之后，如何做？

1. 打开mysql数据库的user表，只保留host为localhost的记录。

   update user set host='localhost' where user = 'root';
   delete from user where user != 'root';
   flush privileges;

2. 查看自已是否打开了mysql的binlog。（引用）

© Sunplace,2019

1. 打开Word（2016），选中数据。
2. 插入-表格-文本转换成表格
3. 列数填入1，如果没有分割符（;,、），默认选择段落标记
4. 将非数据行删除，复制到Excel里
5. *使用Navicat for MySQL，复制待插入的表table_a为table_a_copy
6. *删除table_a_copy的unique索引。
7. *使用导入向导将数据导入table_a_copy
8. *把table_a_copy转储为table_a_copy.sql文件
9. *打开table_a_copy.sql，把drop和create删除，只保留insert
10. *将table_a_copy替换为table_a，insert into替换为insert ignore into
11. *运行转储的table_a_copy.sql
12. 导入MySQL。

*表示存在unique索引