Shulou(Shulou.com)05/31 Report--

How to check the database size in mysql? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

First, if you want to know the size of each database, the steps are as follows:

1. Enter the information_schema database (store the information of other databases)

Use information_schema

2. Query the size of all data:

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables

3. View the size of the specified database:

For example, check the size of the database home

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables where table_schema='home'

4. View the size of a table in the specified database

For example, check the size of the members table in the database home

Select concat (round (sum (data_length/1024/1024), 2), 'MB') as data from tables where table_schema='home' and table_name='members'

2. How does mysql copy table data to another table

1. Copy the table structure and data to the new table

CREATE TABLE new table SELECT * FROM old table

This method copies everything in the oldtable, and of course we can delete it with delete from newtable;.

However, one of the worst aspects of this approach is that the new table does not have the primary key, Extra (auto_increment) and other properties of the old table. You need to add it with "alter" yourself, and it's easy to make a mistake.

2. Only copy the table structure to the new table

CREATE TABLE new table SELECT * FROM old table WHERE 1 # 2

Or CREATE TABLE new table LIKE old table

3. Copy the data from the old table to the new table (assuming the structure of the two tables is the same)

INSERT INTO new table SELECT * FROM old table

4. Copy the data from the old table to the new table (assuming that the structure of the two tables is different)

New INSERT INTO table (field 1, field 2,... (.) SELECT field 1, field 2,... FROM old watch

5. You can copy the structure of Table 1 to Table 2

SELECT * INTO Table 2 FROM Table 1 WHERE 1

6. You can copy all the contents of Table 1 to Table 2.

SELECT * INTO Table 2 FROM Table 1

7. Show create table old table

This lists the creation commands for the old table. We just need to copy the command and change the name of table to create an identical table.

8 、 mysqldump

Use mysqldump to dump the table, rename it and import it back, or run it directly on the command line.

This is the answer to the question about how to view the database size in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.