Mysql Notes

1. mysql int tinyint(1)
2. truncate table 和delete * from的区别
   区别主要两个.
   
   • Truncate 是整体删除， delete是逐条删除
   • truncate不写服务器log，delete写服务器log，这就是为什么truncate要快过delete 所以，影响有：
     
     1. truncate 快
     2. truncate不激活 trigger
     3. truncate 重置 Identity
3. sql where 1=1和 0=1 的作用
   where 1=1; 这个条件始终为True，在不定数量查询条件情况下，1=1可以很方便的规范语句。

4. mysql为字段值添加或者去除前缀、后缀

   • 添加前缀
     update ecs_goods set goods_name=concat('新中式',goods_name) where cat_id =4;
   • 添加后缀
     update ecs_goods set goods_name=concat(goods_name,'新中式') where cat_id =4;
   • 删除
     update ecs_goods set goods_name=right(goods_name,length(goods_name)-1) where cat_id =4;
     其中ecs_goods为表名，cat_id为分类字段名，goods_name为产品字段名
     
（1）加前缀
--专题
update t_mobile_special set image=concat(‘update/zhuanti/show/exclusive/’,image) where type <> 3 and LENGTH(image)>0;
update t_mobile_special set index_image=concat(‘update/zhuanti/show/exclusive/’,image) where type <> 3 and LENGTH
(index_image)>0;
update t_mobile_special set logo=concat(‘update/zhuanti/show/exclusive/’,logo) where type <> 3 and LENGTH(logo)>0;
update t_mobile_special set mobile_image_one=concat(‘update/zhuanti/show/exclusive/’,mobile_image_one) where type <> 3 and
LENGTH(mobile_image_one)>0;
update t_mobile_special set mobile_image_two=concat(‘update/zhuanti/show/exclusive/’,mobile_image_two) where type <> 3 and
LENGTH(mobile_image_two)>0;
（2）替换
–水印
update t_brand_period_banner set url = replace(url,’app.appvipshop.com’,’b.appsimg.com’);
update t_brand_period_banner set url = replace(url,’app.vipshop.com’,’b.appsimg.com’);
–2.0档期
update t_brand_period_sort set banner_image_url = replace(banner_image_url,’app.appvipshop.com’,’b.appsimg.com’) where LENGTH
(banner_image_url)>0;
update t_brand_period_sort set banner_image_url = replace(banner_image_url,’app.vipshop.com’,’b.appsimg.com’) where LENGTH
(banner_image_url)>0;
–3.0档期
update t_brand_mobile_info set banner_image_url = replace(banner_image_url,’app.appvipshop.com’,’b.appsimg.com’) where LENGTH
(banner_image_url)>0;
update t_brand_mobile_info set banner_image_url = replace(banner_image_url,’app.vipshop.com’,’b.appsimg.com’) where LENGTH
(banner_image_url)>0
（3）去前缀
update t_mobile_special set image=right(image,length(image)-30) where type <> 3 and LENGTH(image)>0;
update t_mobile_special set index_image=right(index_image,length(index_image)-30) where type <> 3 and LENGTH(index_image)>0;
update t_mobile_special set logo=right(logo,length(logo)-30) where type <> 3 and LENGTH(logo)>0;


5. MySql之on duplicate key update
   现在有表test，test表中有字段a，在a上有主键或者唯一索引，并且表中只有一条a=1, b=1的数据，现在执行如下的sql：
   insert into test (a,b) values (1,2) on duplicate key update b = b + 1;
   因为a=1的记录已存在了，所以不会执行insert，而会在该条记录上执行update语言b=b+1,记录会变成a=1,b=2
   insert into test (a,b) values (2,2) on duplicate key update b = b + 1;
   a=2的记录不存在，所以执行insert
   来源: http://zifeiwu.com/2013/10/13/mysql-on-duplicate-key-update.html
   MyBatis：

   <insert id="insertForSyn" parameterType="com.vip.mlisting.brand.entity.AppsBrandMobileInfo">    insert into t_apps_brand_mobile_info (brand_id, sale_platform,    brand_type, warehouse, create_time)    values (#{brandId,jdbcType=INTEGER}, #{salePlatform,jdbcType=VARCHAR},    #{brandType,jdbcType=TINYINT}, #{warehouse,jdbcType=VARCHAR},    #{createTime,jdbcType=TIMESTAMP})    ON DUPLICATE KEY UPDATE brand_id =#{brandId}</insert>  

6. mysql的最大连接数
   首先，我们来查看mysql的最大连接数：
   mysql> show variables like'%max_connections%';
   +—————–+——-+
   | Variable_name | Value |
   +—————–+——-+
   | max_connections | 151 |
   +—————–+——-+
   1 row inset(0.00 sec)
   其次，查看服务器响应的最大连接数:
   mysql> show globalstatuslike'Max_used_connections';
   +———————-+——-+
   | Variable_name | Value |
   +———————-+——-+
   | Max_used_connections | 2 |
   +———————-+——-+
   可以看到服务器响应的最大连接数为2，远远低于mysql服务器允许的最大连接数值。
   对于mysql服务器最大连接数值的设置范围比较理想的是：服务器响应的最大连接数值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。
   Max_used_connections / max_connections * 100% = 2/151 *100% ≈ 1%
   如何设置这个最大连接数值。
   方法1：
   mysql>setGLOBALmax_connections=256;
   Query OK, 0 rowsaffected (0.00 sec)
   mysql> show variables like’%max_connections%’;
   +—————–+——-+
   | Variable_name | Value |
   +—————–+——-+
   | max_connections | 256 |
   +—————–+——-+
   1 row inset(0.00 sec)
   方法2：
   修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值：
   max_connections=128
   重启mysql服务即可。
   mysql> show processlist; 可以显示前100条连接信息 show full processlist; 可以显示全部。随便说下，如果用普通账号登录，就只显示这用户的。注意命令后有分号。
7. What’s the difference between utf8_general_ci and utf8_unicode_ci:http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci#
   mysql utf8mb4与emoji表情:http://my.oschina.net/wingyiu/blog/153357?fromerr=nW9DdJtO

函数

1. mysql TO_DAYS(date) 函数
   TO_DAYS(date)
   给定一个日期date, 返回一个天数 (从年份0开始的天数 )。
   mysql> SELECT TO_DAYS(950501);
   -> 728779
   mysql> SELECT TO_DAYS(‘1997-10-07′);
   -> 729669
   TO_DAYS() 不用于阳历出现(1582)前的值，原因是当日历改变时，遗失的日期不会被考虑在内。
   请记住， MySQL“日期和时间类型”中的规则将日期中的二位数年份值转化为四位。例如， ‘1997-10-07′和 ‘97-10-07′ 被视为同样的日期:
   mysql> SELECT TO_DAYS(‘1997-10-07′), TO_DAYS(‘97-10-07′);
   -> 729669, 729669
   对于1582 年之前的日期(或许在其它地区为下一年 ), 该函数的结果实不可靠的。
2. MySql中GROUP_CONCAT()函数
   http://www.111cn.net/database/mysql/50837.htm

索引

涉及频繁硬删除的表尽量不加索引，且索引字段取值范围少!

查询

mysql查看数据库和表的占用空间大小
http://xiaosu.blog.51cto.com/2914416/687835

MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：
TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

1. 看数据库的大小
   use 数据库名
   SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH)
FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';
   得到的结果是以字节为单位，除1024为K，除1048576为M。
2. 看表的大小
   SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'

3. 查看表结构
   desc tablename
show create table tablename

4. 不用join，对于1对多或者多对多情况，需要循环查询，或者表需要冗余很多字段，而冗余字段一致性的维护和更新，会有大量的批操作，这样会不会有性能问题
   1对多，分两次查询。多对多，分页，IN，如果实在搞不定，只能一页for 多次查询（例如20次），至少这个CPU是耗在服务层，可以随时加机器扩展。

日志

mysql有4种不同的日志，分别是二进制日志，查询日志，慢查询日志和错误日志
http://www.blogjava.net/dongbule/archive/2010/09/04/331050.html

数据类型

• int(2)和int(11)不是长度和范围的区别，只是前面会补0

表设计

• MYSQL字符型数据初始值，default 为 null 好还是空串好？
  NULL 类型比较特殊，SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异，会进入索引中，但如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。 
很多人觉得 NULL 会节省一些空间，所以尽量让NULL来达到节省IO的目的，但是大部分时候这会适得其反，虽然空间上可能确实有一定节省，倒是带来了很多其他的优化问题，不但没有将IO量省下来，反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL，也是一个很好的表结构设计优化习惯。
• utf8建议用utf8mb4（四个字节，支持emoji表情字符）

数据结构

• int、bigint、smallint 和 tinyint范围
  使用整数数据的精确数字数据类型。
  
  • bigint
    从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
  • int
    从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。
  • smallint
    从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。
  • tinyint
    从 0 到 255 的整型数据。存储大小为 1 字节。
    注释
    在支持整数值的地方支持 bigint 数据类型。但是，bigint 用于某些特殊的情况，当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。在 SQL Server 中，int 数据类型是主要的整数数据类型。
    在数据类型优先次序表中，bigint 位于 smallmoney 和 int 之间。
    只有当参数表达式是 bigint 数据类型时，函数才返回 bigint。SQL Server 不会自动将其它整数数据类型（tinyint、smallint 和 int）提升为 bigint。
    reference :http://www.cnblogs.com/hsapphire/archive/2009/08/21/1551304.html

---

主从复制

• 复制如何工作
  从高层来看，复制分成三步：
  
  1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
  2. slave将master的binary log events拷贝到它的中继日志(relay log)；
  3. slave重做中继日志中的事件，将改变反映它自己的数据。
  4. MySQL主从复制与读写分离:http://www.cnblogs.com/luckcs/articles/2543607.html

---

• Innodb共享表空间VS独立表空间
  :http://wubx.net/innodb共享表空间vs独立表空间/

• mysql 的 utf8 编码的一个字符最多3个字节，但是一个emoji表情为4个字节，所以utf8不支持存储emoji表情。但是utf8的超集utf8mb4一个字符最多能有4字节，所以能支持emoji表情的存储。