MySQL基础

环境信息

查看版本

mysql>  select version();
+------------+
| version()  |
+------------+
| 5.6.32-log |
+------------+
1 row in set (0.00 sec)

或者：

mysql>  status;
--------------
mysql  Ver 14.14 Distrib 5.6.32, for Win32 (AMD64) 

支持的引擎

查看Mysql支持的数据引擎：

show engines; 

Engines	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine

查看默认的数据库引擎：

show variables like 'default_storage_engine'

Variable_name	Value
default_storage_engine	InnoDB

远程登录

修改配置文件my.cnf。debian中为/etc/mysql/my.cnf

注释掉：

bind-address = 127.0.0.1

重启：

sudo service mysql stop

sudo service mysql start

备份

mysqldump.exe --defaults-extra-file="d:\users\qwshan\appdata\local\temp\tmp5w4sia.cnf"  --user=us_dev_yy_feng --max_allowed_packet=1G --host=pub.mysql.db.dev.sh.ctripcorp.com --port=28747 --default-character-set=utf8 --single-transaction=TRUE --no-data "htlcrawlerresultmdb"

定时备份脚本：

echo "===备份开始==="
mysqldump -uname -p****** databasename | gzip > /databak/filename_$(date +%Y%m%d_%H%M%S).sql.gz
# 删除10天前备份的数据
find /databak -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo "===备份成功==="

加入定时任务：

crontab -e
# 录入需要执行的任务
* 每天凌晨1：30分执行一次
30 1 * * *  /databak/databak.sh

数据表

建表

查看建表的语句：

show create table `jadedungeon`.`articles`;

# Table, Create Table
articles, CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author` varchar(200) DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `fulltext_content` (`content`),
  FULLTEXT KEY `fulltext_title` (`title`),
  FULLTEXT KEY `fulltext_title_body` (`title`,`content`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4

修改表的引擎

ALTER TABLE user ENGINE=MyISAM;

mysql简单分表

用myIsam的垂直分表

只有myISAM能用，挫。

CREATE TABLE IF NOT EXISTS `user1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `gender` int(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; 
 
CREATE TABLE IF NOT EXISTS `user2` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `gender` int(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
 
CREATE TABLE IF NOT EXISTS `user3` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `gender` int(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
 
drop table `alluser`;
CREATE TABLE IF NOT EXISTS `alluser` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `gender` int(1) NOT NULL DEFAULT '0',
    INDEX(id)
) ENGINE=MERGE UNION=(user2,user3,user1) INSERT_METHOD=LAST AUTO_INCREMENT=1;
 
INSERT INTO `alluser` (`name`, `gender`) VALUES('eee', 0);

按逻辑水平分表

在大中型项目中，在数据库设计的时候，考虑到数据库最大承受数据量， 通常会把数据库或者数据表水平切分，以降低单个库，单个表的压力。 我这里介绍两个我们项目中常用的数据表切分方法。 当然这些方法都是在程序中使用一定的技巧来路由到具体的表的。 首先我们要确认根据什么来水平切分？在我们的系统（SNS）中， 用户的UID贯穿系统，唯一自增长，根据这个字段分表，再好不过。

方法一：使用MD5哈希

做法是对UID进行md5加密，然后取前几位（我们这里取前两位）， 然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。

function getTable( $uid ){ \(ext = substr ( md5(\)uid) ,0 ,2 ); return "user_".$ext; }

通过这个技巧，我们可以将不同的UID分散到256中用户表中， 分别是user_00,user_01 …… user_ff。因为UID是数字且递增，根据md5的算法， 可以将用户数据几乎很均匀的分别到不同的user表中。

但是这里有个问题是，如果我们的系统的用户越来越多，势必单张表的数据量越来越大， 而且根据这种算法无法扩展表，这又会回到文章开头出现的问题了。

方法二：使用移位

public function getTable( $uid ) {
       return "user_" . sprintf( "%04d", ($uid >> 20) );}

这里，我们将uid向右移动20位，这样我们就可以把大约前100万的用户数据放在第一个表user_0000, 第二个100万的用户数据放在第二个表user_0001中，这样一直下去，如果我们的用户越来越多，直接添加用户表就行了。 由于我们保留的表后缀是四位，这里我们可以添加1万张用户表， 即user_0000,user_0001 …… user_9999。一万张表，每张表100万数据， 我们可以存100亿条用户记录。当然，如果你的用户数据比这还多，也不要紧， 你只要改变保留表后缀来增加可以扩展的表就行了，如如果有1000亿条数据，每个表存100万， 那么你需要10万张表，我们只要保留表后缀为6位即可。

上面的算法还可以写的灵活点：

/**

• 根据UID分表算法
• @param int $uid //用户ID
• @param int $bit //表后缀保留几位
• @param int $seed //向右移动位数 */ function getTable( \(uid , \)bit , $seed ){ return "user_" . sprintf( "%0{\(bit}d" , (\)uid >> $seed) );}

总结：

上面两种方法，都要对我们当前系统的用户数据量做出可能最大的预估，并且对数据库单个表的最大承受量做出预估。

比如第二种方案，如果我们预估我们系统的用户是100亿，单张表的最优数据量是100万，那么我们就需要将UID移动20来确保每个表是100万的数据，保留用户表（user_xxxx）四位来扩展1万张表。

又如第一种方案，每张表100万，md5后取前两位，就只能有256张表了，系统总数据库就是：256*100万；如果你系统的总数据量的比这还多，那你实现肯定要MD5取前三位或者四位甚至更多位了。

两种方法都是将数据水平切分到不同的表中，相对第一种方法，第二种方法更具扩展性。。。

多表关联更新删除操作

注意事项： 1、尽量不要用IN来包含ID标识执行查询，那样会使索引失效 2、执行语句之前先用SELECT查询获取COUNT看执行前后是否一致

执行方法格式如下：

join...on...写法

update

UPDATE TABLE_1 A, TABLE_2 AS B SET A.PARAM_VALUE = 1, B.PARAM_VALUE = 1 WHERE A.TID = B.TID AND 1 = 1;

delete

DELETE TABLE_1 AS A, TABLE_2 AS B FROM TABLE_1 AS A, TABLE_2 AS B WHERE A.TID = B.TID AND 1 = 1;

where a.id = b.id 链接写法

update

UPDATE TABLE_1 AS A LEFT JOIN TABLE_2 B ON A.TID = B.TID SET A.PARAM_VALUE = 0 WHERE 1 = 1;

delete

DELETE TABLE_1 AS A, TABLE_2 AS B FROM TABLE_1 AS A LEFT JOIN TABLE_2 AS B ON A.TID = B.TID WHERE 1 = 1;

Update with Select

update related_category INNER JOIN product_category
	ON related_category.rel_cat_id = product_category.cat_id
	SET related_category.rel_cat_name = product_category.cat_name