当前位置 : 首页 » 文章分类 :  开发  »  MySQL-Programs 程序

MySQL-Programs 程序

mysql, mysqldump 等命令行工具

Chapter 4 MySQL Programs
https://dev.mysql.com/doc/refman/5.7/en/programs.html


mysql命令

4.5.1 mysql — The MySQL Command-Line Client
https://dev.mysql.com/doc/refman/5.7/en/mysql.html

mysql 客户端命令:
mysql [-h host] [-P port] -u {user} -p{password} {database}
-h, --host=name 要连接的远程主机名或ip地址,默认是 localhost
-P, --port=# 大写P表示数据库端口号,默认是 3306
-u, --user=name 用户名
-p, --password[=name] 小写p表示密码,如果不提供-p密码项,会在交互命令行中提示输入
-D, --database=name 指定登录后的数据库名,例如 -D user
-N, --skip-column-names 输出结果不打印列名
-e, --execute=name 在 MySQL 外部执行 SQL

注意
1、-h, -P, -u 参数前可以有空格也可以没有空格,但是如果 -p 后带有用户密码,那么 -p 与密码之间必须没有空格,否则让你重新输入密码
2、命令最后如果加数据库名,则登录后直接切换为命令中的数据库,否则登录后的当前数据库为 none, 如果最后加 db 名没有自动切换,用 -D 显示指定下。

例1、不显示指定密码
mysql -h mysql.myhost.com -u root -p 回车后输入密码

例2、-p后直接跟密码登录
mysql -h mysql.myhost.com -u root -p123456 -D database

例3、mysql命令的选项和值是可以挨在一起的
mysql -h127.0.0.1 -P3306 -uroot -p123456 -Ddatabase

例4、如果mysql密码中有特殊字符,可以将密码用单引号引起来
mysql -hlocalhost -P5535 -uroot -p'O6~4myloveXdodjf)S(+4*yjQ&8dfdsf' db

例5、MySQL安装完成后会自动提供一个不带密码的root用户,使用root账号登录mysql:
mysql -u root


-hlocalhost连接报错

现象:
mysql -hlocalhost -P8306 -uroot -pxxx 连接报错

mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

但改为 mysql -h127.0.0.1 -P8306 -uroot -pxxx 可成功连接

原因:
首先要知道 mysql -hlocalhost 和 -h127.0.0.1 的区别
mysql -h 127.0.0.1 的时候,使用 TCP/IP 连接
mysql -h localhost 的时候,不使用 TCP/IP 连接,而使用Unix Domain Socket
所以,使用 -hlocalhost 连接报错的原因就是找不到 socket 文件,可能原因是 docker 中的 mysql socket 文件和宿主机上不同,且文件没有映射。

解决:
使用 -h127.0.0.1 连接就行


mysql -e 命令行执行SQL

mysql -hlocalhost -P5535 -uroot -p123 -e 'show tables;' 不登录 MySQL 直接在外部用命令行执行 SQL, 常用于 shell 脚本中操作数据库。
-N 参数可以去掉列头,方便 shell 中直接获取 SQL 结果。

mysql -e "show tables;"
+--------------------+
| Tables_in_commerce |
+--------------------+
| corder             |
| customer           |
| product            |
+--------------------+

mysql -N -e "show tables;"
+----------+
|   corder |
| customer |
|  product |
+----------+

mysqldump数据库(表)导入导出

远程数据库(表)导出到本地数据库(表)文件

导出数据库/表

导出的其实是一个 .sql 脚本,直接记事本就可以打开,里面有建库建表语句, insert 数据语句
(1)导出数据库

-- 将192.168.1.1主机上的mydb数据库导出到本地的mydb.bak文件中
mysqldump -h192.168.1.1 -P3306 -uroot -p123456 --databases mydb > mydb.bak;

-- 将本地mysql服务器上的mydb1数据库导出到本地的mydb1.bak文件中
mysqldump  -uroot -p123456 --databases mydb1 > mydb1.bak;

(2)导出数据表

-- 将192.168.1.1主机上的mydb数据库的tb1数据表导出到本地的tb1.bak文件中
mysqldump -h192.168.1.1 -P3306 -uroot -p123456 mydb tb1 > tb1.bak;

-- 将本地主机上的mydb1数据库的tb2数据表导出到本地的tb2.bak文件中
mysqldump  -uroot -p123456 mydb1 tb2 > tb2.bak;

导出为多条insert语句

默认 mysqldump 会将多条插入语句导出成一条多个 value 的 insert 语句
在mysqldump时加上参数 --skip-extended-insert 即可导出为多条 insert 语句
--extended-insert, -e 表示使用具有多个VALUES列的INSERT语法。这样使导出文件更小,并加速导入时的速度。默认为打开状态,使用 --skip-extended-insert 取消选项。

mysqldump -h localhost -u user -ppsswd --skip-extended-insert blog comment > blog.comment.sql

导入数据库/表

(1)导入数据库
在本地数据库中创建相对应导出的数据库mydb同名的数据库:

create database mydb;

然后退出数据库,再使用以下的 命令导入数据库文件mydb.bak到本地数据库mydb中:

mysql -uroot -p123456 mydb < /root/data/mydb.bak;

或者进入mysql中,使用sourc指令完成数据库导入,如下:

mysql> source  /root/data/mydb.bak;

/root/data/mydb.bak 是远程数据库mydb导出文件的本地存放位置

(2)导入数据表
在本地数据库中创建相对应导出的数据库mydb同名的数据库:
mysql> create database mydb;
切换到 mydb 数据库
use mydb;

然后在mysql中使用source指令来完成数据表的导入,如下:
mysql> source /root/data/tb1.bak;
/root/data/tb1.bak是远程数据表tb1导出文件的本地存放位置

MySQL数据库(表)的导入导出(备份和还原)
https://blog.csdn.net/Deutschester/article/details/6866842

windows上导出文件无法导入

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:
shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.4, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the –result-file option, which creates the output in ASCII format:
shell> mysqldump [options] --result-file=dump.sql

4.5.4 mysqldump — A Database Backup Program
https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

mysqldump命令详解

有3种方式来调用mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3…]
shell> mysqldump [options] –all–database
如果没有指定任何表或使用了—database或–all–database选项,则转储整个数据库。

–host=host_name,-h host_name
从给定主机的MySQL服务器转储数据。默认主机是localhost。

–user=user_name,-u user_name
连接服务器时使用的MySQL用户名。

–password[=password],-p[password]
连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了–password或-p选项后面的 密码值,将提示你输入一个。

–opt
该选项是速记;等同于指定 –add-drop-tables–add-locking –create-option –disable-keys–extended-insert –lock-tables –quick –set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启,但可以用–skip-opt禁用。要想只禁用确信用-opt启用的选项,使用–skip形式;例如,–skip-add-drop-tables或–skip-quick。

–compress,-C
压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。

–lock-tables,-l
开始转储前锁定所有表。

mysqldump命令详解
http://blog.51cto.com/wangwei007/980586

MySQL mysqldump命令
https://www.jianshu.com/p/14947ba6862b


存储过程

查询存储过程

show procedure status like '%xx%';
或者
select * from information_schema.Routines where ROUTINE_NAME=’CountProc1’;

查看存储过程的代码

show create procedure proc_name;

计算多表count之和

t_pic_record_{0-99} 是 100 个分表,统计所有分表中 groupid=in_group_id 的数据个数之和。

DROP PROCEDURE IF EXISTS count_t_pic_record;
delimiter $
create procedure count_t_pic_record(in_group_id varchar(64))
begin
    declare i int;
    set i=0;
    set sum=0;
    while i < 99 do
        set cnt=0;
        select count(*) into cnt from concat('t_pic_record_',i) where groupid=in_group_id;
        sum = sum + cnt;
    end while;
    select sum;
end $

while循环批量插入数据

创建一个存储过程,while 循环插入 100 万 person 数据,同时给每个人打 3 个标签插入 person_tag 关联表

DROP PROCEDURE IF EXISTS batch_insert_person;
delimiter $
create procedure batch_insert_person()
begin
    declare num int;
    set num=1;
    while num < 1000000 do
        -- 随机姓名 可根据需要增加/减少样本
        set @SURNAME = '王李张刘陈杨黄赵吴周徐孙马朱胡郭何高林罗郑梁谢宋唐位许韩冯邓曹彭曾萧田董潘袁于蒋蔡余杜叶程苏魏吕丁任沈姚卢姜崔钟谭陆汪范金石廖贾夏韦傅方白邹孟熊秦邱江尹薛阎段雷侯龙史陶黎贺顾毛郝龚邵万钱严覃武戴莫孔向汤';
        set @NAME = '丹举义之乐书乾云亦从代以伟佑俊修健傲儿元光兰冬冰冷凌凝凡凯初力勤千卉半华南博又友同向君听和哲嘉国坚城夏夜天奇奥如妙子存季孤宇安宛宸寒寻尔尧山岚峻巧平幼康建开弘强彤彦彬彭心忆志念怀怜恨惜慕成擎敏文新旋旭昊明易昕映春昱晋晓晗晟景晴智曼朋朗杰松枫柏柔柳格桃梦楷槐正水沛波泽洁洋济浦浩海涛润涵渊源溥濮瀚灵灿炎烟烨然煊煜熙熠玉珊珍理琪琴瑜瑞瑶瑾璞痴皓盼真睿碧磊祥祺秉程立竹笑紫绍经绿群翠翰致航良芙芷苍苑若茂荣莲菡菱萱蓉蓝蕊蕾薇蝶觅访诚语谷豪赋超越轩辉达远邃醉金鑫锦问雁雅雨雪霖霜露青靖静风飞香驰骞高鸿鹏鹤黎';
        -- length(@surname)/3 是因为中文字符占用3个长度
        set @FULL_NAME = concat(substr(@surname,floor(rand()*length(@surname)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1), substr(@NAME,floor(rand()*length(@NAME)/3+1),1));
        set @id_no = CONCAT('1',CEILING(RAND()*9000000000+1000000000));
        set @rand_conf = convert(rand() * 100, decimal(10,2));
        set @arch_id = CEILING(RAND()*9000000000+1000000000);
        set @person_no = substring(MD5(RAND()),1,15);
        set @arch_cover = substring(MD5(RAND()),1,24);
        set @rand_tag_id = floor(rand() * 27);
        insert into person(archive_cover, archive_id, confidence, id_card, id_photo_address, last_modified_date, manual_process, person_number, real_info_modified_date, real_name)
            values(@arch_cover, @arch_id, @rand_conf, @id_no,'http://image.masikkk.com/idcard', now(),0, @person_no, now(),@FULL_NAME);
        insert into person_tag(person_id, tag_id) values
            (LAST_INSERT_ID(), @rand_tag_id), (LAST_INSERT_ID(), @rand_tag_id+1), (last_insert_id(), @rand_tag_id-1);
        set num=num+1;
    end while;
end $
call batch_insert_realinfos();

执行sql脚本

Mysql命令行执行.sql脚本有两种方式

mysql < xx.sql(未登录mysql)

1、在未连接数据库的情况下
mysql -h localhost -uroot -ppwd database_name < prod.sql

例如执行一个批量sql脚本并统计执行时间

time mysql -h localhost -uroot -ppwd database_name < prod.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
real    6m13.689s
user    0m30.067s
sys    0m11.806s

source xx.sql(已登录mysql)

2、在已经连接数据库的情况下,使用 source 命令 加 脚本全路径
source /tmp/prod.sql
其中 /tmp/prod.sql 是登录 mysql 的机器上的 sql 脚本


拷贝创建新表

创建新表同时拷贝数据(丢失约束等)

复制表结构及数据到新表
CREATE TABLE 新表 SELECT * FROM 旧表
这种方法会将oldtable中所有的内容都拷贝过来,当然我们可以用delete from newtable;来删除。
不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用alter table语句添加,而且容易搞错。

只拷贝表结构(丢失约束等)

只复制表结构到新表
CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2
其中的where只要是个恒为false的表达式即可,此方法同样会丢失约束等额外信息

只拷贝表结构(保留约束等)

CREATE TABLE 新表 LIKE 旧表
此方法会保留表的约束等所有额外信息,推荐使用

创建新表并复制数据(保留约束等)

完整复制表的方法:
先创建新表,保留约束等,在插入旧表的所有数据
CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

例如

create table user_bk like user;
insert into user_bk select * from user;

MySQL复制表结构表数据
https://www.cnblogs.com/emanlee/p/5140670.html


UDF自定义函数

6.2 Adding a Loadable Function
https://dev.mysql.com/doc/extending-mysql/5.7/en/adding-loadable-function.html

User Defined Function(UDF) 允许用户自定义函数并以插件 plugin 的形式添加到 MySQL 中。

1、c/c++编写自定义函数
MySQL UDF 函数只支持用 c/c++ 编写。完成后编译为 udf_example.so 动态链接库文件。

2、将 udf_example.so 动态链接文件拷贝到 MySQL 的 plugin 目录下
plugin 目录可以通过变量 plugin_dir 查看,

mysql> show variables  like '%plugin_dir%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| plugin_dir    | /usr/local/opt/mysql@5.7/lib/plugin/ |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

3、安装函数
根据函数的返回类型,在 MySQL 命令行中执行下面的语句来安装函数,以安装返回 INTEGER 类型的 udf_example 函数为例:

DROP FUNCTION IF EXISTS udf_example;
CREATE FUNCTION metaphon RETURNS INTEGER SONAME 'udf_example.so';

4、删除函数

DROP FUNCTION udf_example;

上一篇 MySQL-INFORMATION_SCHEMA

下一篇 MySQL-Function 函数与操作符

阅读
评论
3.1k
阅读预计13分钟
创建日期 2021-07-19
修改日期 2021-07-19
类别

页面信息

location:
protocol:
host:
hostname:
origin:
pathname:
href:
document:
referrer:
navigator:
platform:
userAgent:

评论