Using the command line
You can use the mysql command-line program to determine the sizes of MySQL databases and tables. To do this, follow these steps:
- Log in to your account using SSH command
- At the command line, type the following command, replacing username with your valid DB login name
mysql -u username -p
- At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.
- To determine the sizes of all of your databases, at the mysql> prompt type the following command:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
GROUP BY table_schema;
Depending on how many databases you have and how large they are, this command may take a minute or two to complete. After the command finishes, it displays a list of all of your databases and their corresponding size (in megabytes).
- To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check:
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;
After the command finishes, it displays a list of all of the tables and their corresponding size (in megabytes), with the largest table at the top and smallest table at the bottom.
Caused by: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 42 to TIMESTAMP.
In hibernate data source mapping property please add the highlighted property
By default these invalid date will be converted into null values when it comes to Java object.
- exception (the default), which throws an SQLException with an SQLState of S1009.
- convertToNull, which returns NULL instead of the date.
- round, which rounds the date to the nearest closest value which is 0001-01-01.
ometimes you have table in your system that has only one record like Configuration, Setup, Settings tables.
You want to prevent users from inserting more than one record in this table, To do this you can use the below INDEX .
CREATE UNIQUE INDEX ONE_ROW_INDEX
ON TABLE_NAME (1);
If you try to insert more than one record in previous table you will get below exception
ORA-00001: unique constraint (SCHEMA.ONE_ROW_ALLOWED) violated
You could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this:
show table status like ‘%’;
you’ll find a column called Data_length which tells you “the length of the data file” for each table. If you simply add the size of each table in the database together, you should have the size of the whole database.
If you run the query which is given below in MySQL Query Browser then you will get the two columns first will display the Data Base Name and the second will display the Data Base Size in MB.
SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;
Import CSV file directly into MySQL
Posted by katy8439 in MySQL
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.
To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.
You can then import it into a MySQL table by running:
load data local infile ‘uniq.csv’ into table tblUniq fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(uniqName, uniqCity, uniqComments)
The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.