MySql学习（MariaDb）

资料

http://www.cnblogs.com/lyhabc/p/3691555.html

 http://www.cnblogs.com/lyhabc/p/3691555.html

MariaDb

官网： https://mariadb.org/

安装地址: https://downloads.mariadb.org/mariadb/repositories

1. MySql .net Connector 在执行完命令后，关闭连接， 字符串中的 Password 会被清空。 设置带有 Password  的新字符串，连接字符串也不显示，但可以打开。

2. 我使用的是 HeidiSql客户端， 在使用 group_concat 函数时报错， 调试发现， group_concat 函数后面有一个空格，如果去除空格，则正确。不知道是客户端的原因，还是MySql的原因。

3. MySqlWorkbench 自定义快捷键： http://blog.csdn.net/stableboy/article/details/45887035

4. 国内镜像： 

5. 连接： mysql -uroot -p1234

 

Windows安装

安装多份实例 ， 拷贝多份 my.ini 目录。运行：

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --install MySql57_2 --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7_2\my.ini"

linux设置

查看状态： service mysql status

启动服务： service mysql start

 

对于mariadb新版： systemctl restart mariadb.service

 

转移数据库

http://blog.csdn.net/renfufei/article/details/17616549

 

查看配置

查找位置 

which mysqld

查看配置

/usr/sbin/mysqld --verbose  --help  

找出配置文件地址：

/usr/sbin/mysqld --verbose --help  | grep -A1 'Default options'

 

-A1  显示查找到该行以及该行之后的 after 1行。

大小写

设置MySql 区分大小写：http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names

　　1)找到Mysql服务的配置文件，它在服务启动参数中： --defaults-file  ，默认是：C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

　　2)在my.ini，在  [mysqld] 下添加： lower_case_table_names=2

　　3)重启MySql服务。

 

0：存储为指定，大小写比较。 特殊情况：Windows，Mac 不能指定为0

1: 存储为小写。

2：存储为给定的名称，但使用小写比较。

 

CentOs7+mariadb10.0 路径： /etc/my.cnf.d/server.cnf

 Ubuntu14+ mariadb10.1 路径： /etc/mysql/my.conf

设置远程连接

参考：http://www.cnblogs.com/24la/p/mariadb-remoting-access.html

 

在本地打开mysql，执行：

use mysql;

select * from user\G;  #查看 user = 'root' 的记录。 \G 表示一行一段的展示，方便阅读。

GRANT ALL PRIVILEGES ON *.* to 'root'@'%' identified by '123456';  

flush privileges;

 

如果上面的办法不行（影响行数为0），则可以修改配置文件。把 my.conf 里的 bind-address一行注释掉。

 

添加用户

程序执行时的用户及权限：

CREATE USER 'testuser'@'%' IDENTIFIED BY '1234';
GRANT USAGE ON *.* TO 'testuser'@'%';
GRANT SELECT, EXECUTE,CREATE,  CREATE TEMPORARY TABLES, DELETE, INSERT,  UPDATE, LOCK TABLES ON `test`.* TO 'testuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'testuser'@'%';

 

客户端管理者的权限：

CREATE USER 'testuser'@'%' IDENTIFIED BY '1234';
GRANT USAGE ON *.* TO 'testuser'@'%';
GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON `test`.* TO 'testuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'testuser'@'%';

 

修改密码

先看用户在哪些主机上定义。

SELECT `user`, `host`  FROM `mysql`.`user`;

 

SET PASSWORD FOR 'root'@'%' = PASSWORD('lwl417');

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('lwl417');
FLUSH PRIVILEGES;

编码

MySql编码最奇葩，标准编码： utf8mb4

collation:  utf8mb4_general_ci

characterset: utf8mb4

非主键更新删除报错

http://jingyan.baidu.com/article/e5c39bf58ed69239d76033a4.html

执行：

SET SQL_SAFE_UPDATES = 0;

备份还原

我没有找到好的备份还原方法，MySql 这一点做的实在太差。

备份分为两部分: 一是备份脚本,包含表结构,函数,存储过程,   二是备份Insert数据.

默认情况下,导出数据库和 Insert 数据 .不包含存储过程和函数 . 

一. 脚本:

mysqldump --host=server --user=root --password=1234 --no-data  --routines    DataBaseName > FileName

-d, --no-data   是不生成Insert语句

-R, --routines 是包含存储过程和函数

二. 仅 insert语句.

mysqldump -u root -t   DataBaseName > FileName

-t, --no-create-info , 在默认的基础上,去除 create sql , 就剩 insert sql 了. 

 

恢复:

mysql -uroot -p"db_password" -f dbname < backup.sql  

-f 参数表示在导出过程中忽略出现的SQL错误

主从 数据同步设置

http://369369.blog.51cto.com/319630/790921/

 

快速复制数据库

mysqldump -h ${源服务器IP}  -uroot -p123456  ${源数据库} --add-drop-table | mysql -h ${目标数据库IP}  -u root -p123456  ${目标数据库}

如果要忽略某些表, mysql dump 时添加:
--ignore-table=database.table1 --ignore-table=database.table2

https://blog.csdn.net/whatlookingfor/article/details/51942566

语法

1.函数模板：

 delimiter  $$

CREATE function  CompareStr (FirstVal varchar(1000),SecondVal varchar(1000) )
returns int
begin
    
     if ( FirstVal is null )  then 
         return -1 ;  
    end if;
    
    if ( SecondVal is null )  then 
         return -1 ;  
    end if;
    
    set FirstVal = lower(rtrim(ltrim(FirstVal)));
    set SecondVal = lower(rtrim(ltrim(SecondVal)));    
    
    
    if (FirstVal = SecondVal ) then 
        return 0;
    else 
        return 1;
    end if;
 
end
$$ 
DELIMITER ;
 

 

If ( condition) then   statement  elseif ( condition ) then statement  end if ; 最后的 ; 一定要有。

2.存储过程模板

DELIMITER $$
CREATE  PROCEDURE `S_CreateVarTable`(VarTable varchar(50) , VarName   varchar(50), VarValue varchar(9)  )
label_pro:
begin

set @CorpTable = replace(VarTable , concat( '{' , VarName ,'}' ),  concat('{' , VarValue , '}')) ; 

if exists (select 1 from information_schema.Tables where table_schema= database() and  table_Name = @CorpTable ) then
    leave label_pro;
end if;

set @sql =  N'
create table `{InstanceTable}` (
select *
from `{VarTable}`
limit 0,0 );
';

set @sql = replace(@sql,'{VarTable}',   VarTable) ;
set @sql = replace(@sql,'{InstanceTable}',  @CorpTable) ;
 
prepare smt from @sql ;
execute smt ;
 
end$$
DELIMITER ;

 

3. 修改自增种子值： alter table users AUTO_INCREMENT=10000;

4. 计算列：

https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

http://www.linuxidc.com/Linux/2016-02/128066.htm

在创建列类型后面添加：  GENERATED ALWAYS AS ($表达式$)  

如果是计算列， MySql 有两种方式：Virtual 和 Stored。  MariaDb 称为： Virtual 和 Presistent

5. 血的教训.  分页必须要有 order by 

诡异的情况:   group by  broker_id , dealer_id   limit #{take} , #{skip}  获取所有的数据时, 出现重复数据.