更新操作报错mysql_mysql优秀sql语句收集，mysql常用日常操作及mysql常见报错解决方案，持续更新！...

mysql常见报错解决方案

删除带外键的表【foreign key constraint fails】报错

SET foreign_key_checks = 0;  // 先设置外键约束检查关闭

drop table table1;  // 删除表，如果要删除视图，也是如此

SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性

mysql常用日常操作

生成所有truncate语句

先执行select语句，生成所有truncate语句。

语句格式：

select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where  table_schema in ('数据库1','数据库2');

计算MySQL中各个数据库的占用大小

SELECT

TABLE_SCHEMA,

CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),' MB') AS data_size,

CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size

FROM information_schema.tables

GROUP BY TABLE_SCHEMA

ORDER BY data_length DESC;

更加详细的展示：

mysql> select

-> table_schema as '数据库',

-> sum(table_rows) as '记录数',

-> sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',

-> sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)'

-> from information_schema.tables

-> where table_schema='hivaffic';

+-------------+-----------+------------------+------------------+

| 数据库      | 记录数    | 数据容量(GB)     | 索引容量(GB)     |

+-------------+-----------+------------------+------------------+

| hivaffic | 126870173 |             8.36 |             9.98 |

+-------------+-----------+------------------+------------------+

1 row in set (0.33 sec)

mysqldump备份操作

mysqldump命令的安装

yum -y install holland-mysqldump.noarch

mysqldump使用方法：

mysqldump -h[ip] -P[端口号]  -u[用户名]  -p[密码]  数据库名  表名 >导出的文件名.sql

mysqldump -h[ip] -P[端口号]  -u[用户名]  -p[密码]  数据库名  表名1 表名2 表名3 | gzip >导出的文件名.sql.gz

1.导出数据和表结构

mysqldump  -u用户名  -p   数据库名 >  放置地址/aaa.sql       (回车后输入密码即可)

如：导出整个数据库结构和数据，或者直接在命令行写入密码

mysqldump -h localhost -uroot -pgmcc@JTxxx  hivetraffic > dump_data.sql

2.导出数据库中制定的几张表

mysqldump  -u用户名  -p   数据库名  table1  table2  table3 >  放置地址/aaa.sql       (回车后输入密码即可)

如：导出单个数据表结构和数据

mysqldump -h localhost -uroot -p123456  database table > dump.sql

3.只导出表结构

mysqldump  -u用户名  -p   -d   数据库名 >  放置地址/aaa.sql    (回车后输入密码即可)

如：导出单个数据表结构(不包含数据)

mysqldump -h localhost -uroot -p123456  -d database table > dump.sql

4.导出整个数据库结构(不包含数据)

mysqldump -h localhost -uroot -p123456  -d database > dump.sql

5.导出整个数据库(包括数据和结构)

windows

mysqldump dbname > c:mydb.sql -u root -p

linux

mysqldump -uroot -p --all-databases > /sqlfile.sql

注意：all前面是两个减号(-)，，databases前面是一个减号   --all-databases

mysqldump恢复操作

安装：

yum -y install mysql-client

#或者

apt-get install mysql-client

mysql导入数据操作：

方法1: msyql>source   放置地址/aaa.sql;

mysql> use g6monitor;

mysql> source /app/t_sta_high_emission_vehicle.sql;

方法2: mysql   -u用户名   -p   数据库名 <  放置位置/aaa.sql;  (回车后输入密码即可)

root@8738406079ce:/data# mysql -u root -pxxx -h127.0.0.1 -P yyy db_name --default-character-set=utf8  < /data/xxx.sql

//或者

root@8738406079ce:/data# mysql -u root -p -D service

Enter password:

root@8738406079ce:/data#

查看mysql日志文件位置

登录mysql终端，查看日志文件路径

mysql> show variables like 'general_log_file';

+------------------+------------------------------------+

| Variable_name    | Value                              |

+------------------+------------------------------------+

| general_log_file | /usr/local/mysql/data/localhost.log |

+------------------+------------------------------------+

1 row in set (0.00 sec)

错误日志文件路径

mysql> show variables like 'log_error';

+---------------+------------------------------------+

| Variable_name | Value                              |

+---------------+------------------------------------+

| log_error     | /usr/local/mysql/data/localhost.err |

+---------------+------------------------------------+

1 row in set (0.00 sec)

慢查询日志文件路径

mysql> show variables like 'slow_query_log_file';

+---------------------+-----------------------------------------+

| Variable_name       | Value                                   |

+---------------------+-----------------------------------------+

| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |

+---------------------+-----------------------------------------+

1 row in set (0.01 sec)

MySQL中查询所有数据库名和表名

1.查询所有数据库

show databases;

2.查询指定数据库中所有表名

select table_name from information_schema.tables where table_schema='database_name' and table_type='base table';

3.查询指定表中的所有字段名

select column_name from information_schema.columns where table_schema='database_name' and table_name='table_name';

//或者

desc table_name;

4.查询指定表中的所有字段名和字段类型

select column_name,data_type from information_schema.columns where table_schema='database_name' and table_name='table_name';

//或者

desc table_name;