MySQL查询条件中字符串包含空格的问题

问题
最近在联调某个业务时发现使用的签名总是验证不过,在MySQL中查询了该业务的私钥配置和业务侧的配置是一样的,问题就出在SQL查询这里,最后将配置导出到本地发现私钥后面多了一个空格,将空格删除然后签名计算OK。问题是:为什么在DB查询条件中的字符串没有包含空格也可以查到实际包含空格的这条记录呢?

原因
如果字段是char或varchar类型,那么在字符串比较的时候MySQL使用PADSPACE校对规则,会忽略字段末尾的空格字符,若想做到精确匹配可以使用下面几种方法:
方法1:使用like语句;
方法2:使用binary类型,例如,select binary ‘a’ = ‘a ‘
方法3:再添加一个length()条件,例如,select col from table where col = ‘a ‘ and LENGTH(col) = LENGTH(‘a ‘)

官方手册说明(5.0版本):
http://dev.mysql.com/doc/refman/5.0/en/char.html
11.1.6.1. The CHAR and VARCHAR Types
All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant.

转载地址:https://blog.csdn.net/delphiwcdj/article/details/16983993

insert into a select * from b 锁问题分析

很长一段时间,都认为”insert into a select * from b” 会导致b表被锁住。在最近一次组内同事的质疑中,发现错了.b表会不会被锁,其实和隔离级别有一定关系,下面我们具体分析一下

环境说明:
1.隔离级别:REPEATABLE-READ
2.MySQL版本:5.6.29
3.Binlog格式:ROW
4.建表SQL:

CREATE TABLE `os_diskio_history_bak` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`ip` varchar(50) NOT NULL,
`tags` varchar(100) DEFAULT NULL,
`fdisk` varchar(50) NOT NULL DEFAULT '0',
`disk_io_reads` bigint(18) NOT NULL DEFAULT '0',
`disk_io_writes` bigint(18) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`YmdHi` bigint(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_ymdhi` (`YmdHi`) USING BTREE,
KEY `idx_ip_ymdhi` (`ip`,`YmdHi`),
KEY `idx_io_reads` (`disk_io_reads`),
KEY `idx_io_writes` (`disk_io_writes`)
) ENGINE=InnoDB AUTO_INCREMENT=484556 DEFAULT CHARSET=utf8

测试用例:
1.直接插入

终端1:
mysql> insert into os_diskio_history_bak select * from os_diskio_history;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
929 lock struct(s), heap size 112168, 207989 row lock(s), undo log entries 207063
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1636 lock struct(s), heap size 194088, 363913 row lock(s), undo log entries 362280
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1984 lock struct(s), heap size 226856, 440646 row lock(s), undo log entries 438666

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加

2.按主键降序插入

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by id desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
502 lock struct(s), heap size 63016, 109585 row lock(s), undo log entries 109583
mysql> show engine innodb status;
mysql tables in use 2, locked 2
630 lock struct(s), heap size 79400, 137782 row lock(s), undo log entries 137781
mysql> show engine innodb status;
mysql tables in use 2, locked 2
920 lock struct(s), heap size 112168, 201581 row lock(s), undo log entries 201580
mysql> show engine innodb status;
mysql tables in use 2, locked 2
1240 lock struct(s), heap size 144936, 271567 row lock(s), undo log entries 271566

从上面,我们看到 row lock 和 lock struct 在不停的增长,被锁的行数在不断增加,其实同测试案例1
结论:
通过主键排序或则不加排序字段的导入操作”insert into a select * from b”,是会锁b表,但他的锁是逐步地锁定已经扫描过的记录。
默认:
主键升序的select :从第一行开始扫描到最后,即第一行开始锁直到最后。
主键倒序select:从最后一行开始扫描到最前,即最后一行开始锁直到第一行。

3.B 表使用非主键字段排序

终端1:mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2190 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2191 lock struct(s), heap size 259624, 486689 row lock(s)
mysql> show engine innodb status;
mysql tables in use 2, locked 2
2192 lock struct(s), heap size 259624, 486689 row lock(s), undo log entries 44409

从上面,我们看到 row lock 和 lock struct 值一直没有变,他的锁是一开始就会锁定整张表,不能进行任何操作,直到锁释放了

结论:insert into a select * from b 的导入操作是会锁定原表,但是锁是有2种情况:“逐步锁”,“全锁”

现在我们调整隔离级别为:RC

终端1:
mysql> set tx_isolation='READ-COMMITTED';
mysql> insert into os_diskio_history_bak select * from os_diskio_history order by YmdHi desc;
终端2:
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 41394
mysql> show engine innodb status;
mysql tables in use 2, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 95860

从上面,我们看到 row lock 为 0

总结:
1.在RR模式下:在按主键进行排序或不排序时,采用的是逐步锁定行
2.在RR模式下:在按非主键进行排序时,采用的是表锁
3.在RC模式下:无论按主键还是非主键排序,B表都不加锁

mysql中order by和limit同时使用时存在的问题及解决方法

今天突然遇到一个问题,同时使用order by 和 limit 时存在问题(order by 列存在重复值);
就是当使用 limit 140,10  和 limit 130,10的数据是一样;
按正常理解,两者之间数据应该是不一样
执行sql如下:[同时,我给了测试sql,测试数据以及测试表结构]
SELECT
     p.id,
     p.org_name AS orgName,
     p.city_id AS cityId,
     p.is_cooperation AS isCooperation,
     p.create_time AS createTime,
     p.maintenancer AS creater,
     p.audit_status AS auditStatus
FROM partner_organization p
WHERE  p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY p.create_time DESC
LIMIT 140,10
 
执行后结果集如下:
QQ截图20150228184142
执行如下sql:
SELECT
     p.id,
     p.org_name AS orgName,
     p.city_id AS cityId,
     p.is_cooperation AS isCooperation,
     p.create_time AS createTime,
     p.maintenancer AS creater,
     p.audit_status AS auditStatus
FROM partner_organization p
WHERE p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY p.create_time DESC
LIMIT 130,10
 
则结果集如下:
QQ截图20150228184142
两者的结果集明显一样;
因此,我们推测:
     在同时使用order by和limit时,MySQL进行了某些优化,
     将语句执行逻辑从”where——order by——limit”变成了”order by——limit——where”
 
那么针对这条语句,我们如何进行优化呢,我们采取两种方式:
方式一:我们将排序列,新增一列,确保唯一性来实现limit
SELECT
p.id,
p.org_name AS orgName,
p.city_id AS cityId,
p.is_cooperation AS isCooperation,
p.create_time AS createTime,
p.maintenancer AS creater,
p.audit_status AS auditStatus
FROM partner_organization p
WHERE p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY
p.create_time,id DESC
LIMIT 140,10
方式二:我们用where过滤后形成结果集,作为子查询来处理
SELECT
p.id,
p.org_name AS orgName,
p.city_id AS cityId,
p.is_cooperation AS isCooperation,
p.create_time AS createTime,
p.maintenancer AS creater,
p.audit_status AS auditStatus
FROM (
SELECT     *
FROM partner_organization p
WHERE p.partner_type = 1 AND p.city_id IN (1, 2)
) p

ORDER BY p.create_time DESC
LIMIT 130,10
测试数据下载地址如下:
链接: http://pan.baidu.com/s/1gdy7hQj
密码: icx8

存储过程或数据查询中,用到的函数:截断函数:left,right,substring,trunc;连接函数:concat

1.Mysql字符串截取函数:substring,left,right,substring_index
用法说明:
(1).left:从左开始截取字符串
left(str, length)
说明:left(被截取字段,截取长度)
Ex:select left(time,10) from login_log;
注:这里截取时间长度(从左往右截取)为10:结果会是:2014-01-13
(2).right:从右开始截取字符串
right(str, length)
说明:right(被截取字段,截取长度)
Ex:select right(time,8) from login_log;
注:这里截取时间长度(从右往左截取)为8:结果会是:23:22:10
(3).substring:截取字符串
substring(str, pos)
substring(str, pos, length)
说明:substring(被截取字段,从第几位开始截取)
substring(被截取字段,从第几位开始截取,截取长度)
Ex:select substring(time,6) from login_log;
注:结果为:01-13 23:25:10
select substring(time,6,5) from login_log;
注:结果为:01-13
(4).substring_index:按关键字截取字符串
    substring_index(str,delim,count)
说明:substring_index(被截取字段,关键字,关键字出现的次数)
Ex: select substring_index(“blog@123@acv”,”@”,2) from account
注:结果为:blog@123
2.Mysql 日期增加函数:adddate
ADDDATE(时间,INTERVAL 月份或天数)
ADDDATE(时间,天数)
Ex: select adddate(‘2014-01-13’,interval 31 day);
注:结果为:2014-02-13
select adddate(‘2014-01-13’,2);
注:结果为:2014-01-15
select adddate(‘2014-01-13’,-2);
注:结果为:2014-01-11
3.Mysql字符串连接函数:concat
concat(str1,str2,…)
注:返回结果为连接参数产生的字符串.如有任何一个参数为NULL,则返回值为 NULL.
Ex: select concat(‘kong’,’zhong’);
注:结果为:kongzhong
4.Oracle trunc()函数的用法
**************日期********************
(1).select trunc(sysdate) from dual
结果为:2014-1-13  今天的日期为2014-1-13
(2).select trunc(sysdate, ‘mm’)   from   dual
结果为:2014-1-1    返回当月第一天
(3).select trunc(sysdate,’yy’) from dual
结果为:2014-1-1    返回当年第一天
(4).select trunc(sysdate,’dd’) from dual
结果为:2014-1-13      返回当前年月日
(5).select trunc(sysdate,’yyyy’) from dual
结果为:2014-1-1    返回当年第一天
(6).select trunc(sysdate,’d’) from dual
结果为:2014-1-12  返回当前星期的第一天(默认星期日开始算)
(7).select trunc(sysdate, ‘hh’) from dual
结果为:2014-1-13 23:00:00   当前时间为 2014-1-13 23:50:00
(8).select trunc(sysdate, ‘mi’) from dual
结果为:2014-1-13 23:50:00   当前时间为 2014-1-13 23:50:00
注:TRUNC()函数没有秒的精确
***************数字********************
用法:
TRUNC(number,num_digits)
注解:
Number 需要截尾取整的数字
Num_digits 用于指定取整精度的数字,Num_digits 的默认值为 0
TRUNC()函数截取时不进行四舍五入
(1).select trunc(123.458) from dual
结果为:123
(2).select trunc(123.458,0) from dual
结果为:123
(3).select trunc(123.458,1) from dual
结果为:123.4
(4).select trunc(123.458,-1) from dual
结果为:120
(5).select trunc(123.458,-4) from dual
结果为:0
(6).select trunc(123.458,4) from dual
结果为:123.458
(7).select trunc(123) from dual
结果为:123
(8).select trunc(123,1) from dual
结果为:123
(9).select trunc(123,-1) from dual
结果为:120

[转]千万级别mysql合并表快速去重

目标:

现有表a和b,把两个表中的数据合并去重到c表中。其中a和b表中数据量大概在2千万左右。

基本情况

操作系统版本:CentOS release 5.6 64位

操作系统内存:8G

数据库版本:5.1.56-community 64位

数据库初始化参数:默认

数据库表和数据量

表a:

mysql> desc a2kw;+——-+————-+——+—–+———+——-+

| Field | Type        | Null | Key | Default | Extra |

+——-+————-+——+—–+———+——-+

| c1    | varchar(20) | YES  | MUL | NULL         |

| c2    | varchar(30) | YES     | NULL         |

| c3    | varchar(12) | YES     | NULL         |

| c4    | varchar(20) | YES     | NULL         |

+——-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

表b

mysql> desc b2kw;+——-+————-+——+—–+———+——-+

| Field | Type        | Null | Key | Default | Extra |

+——-+————-+——+—–+———+——-+

| c1    | varchar(20) | YES     | NULL         |

| c2    | varchar(30) | YES     | NULL         |

| c3    | varchar(12) | YES     | NULL         |

| c4    | varchar(20) | YES     | NULL         |

+——-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

 

a和b表的数据概况如下

mysql> select * from a2kw limit 10;+———–+———–+——+———-+

| c1        | c2        | c3   | c4       |

+———–+———–+——+———-+

| 662164461 | 131545534 | TOM0 | 20120520 |

| 226662142 | 605685564 | TOM0 | 20120516 |

| 527008225 | 172557633 | TOM0 | 20120514 |

| 574408183 | 350897450 | TOM0 | 20120510 |

| 781619324 | 583989494 | TOM0 | 20120510 |

| 158872754 | 775676430 | TOM0 | 20120512 |

| 815875622 | 631631832 | TOM0 | 20120514 |

| 905943640 | 477433083 | TOM0 | 20120514 |

| 660790641 | 616774715 | TOM0 | 20120512 |

| 999083595 | 953186525 | TOM0 | 20120513 |

+———–+———–+——+———-+

10 rows in set (0.01 sec)

基本步骤

1、在B表上创建索引

mysql> select count(*) from b2kw;+———-+

| count(*) |

+———-+

| 20000002 |

+———-+

1 row in set (0.00 sec)

mysql> create index ind_b2kw_c1 on  b2kw(c1);

Query OK, 20000002 rows affected (1 min 2.94 sec)

Records: 20000002  Duplicates: 0  Warnings: 0

数据量为:20000002 ,时间为:1 min 2.94 sec

2、把a、b分别插入中间表temp表中

创建中间表mysql> create table temp  select * from c2kw where 1=2;

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

插入数据

mysql> insert into temp  select * from a2kw;

Query OK, 20000002 rows affected (13.23 sec)

Records: 20000002  Duplicates: 0  Warnings: 0

mysql> insert into temp  select * from b2kw;

Query OK, 20000002 rows affected (13.27 sec)

Records: 20000002  Duplicates: 0  Warnings: 0

mysql> select count(*) from temp;

+———-+

| count(*) |

+———-+

| 40000004 |

+———-+

1 row in set (0.00 sec)

数据量为:40000004 ,时间为:26.50 sec

3、temp建立联合索引,强制索引去掉重复数据

mysql> create index ind_temp_c123 on temp(c1,c2,c3);Query OK, 40000004 rows affected (3 min 43.87 sec)

Records: 40000004  Duplicates: 0  Warnings: 0

查看执行计划

mysql> explain select c1,c2,c3,max(c4) from temp FORCE INDEX (ind_temp_c123) group by c1,c2,c3 ;

+—-+————-+——-+——-+—————+—————+———+——+———-+——-+

| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows     | Extra |

+—-+————-+——-+——-+—————+—————+———+——+———-+——-+

1 | SIMPLE      | temp  | index | NULL          | ind_temp_c123 | 71      | NULL | 40000004 |       |

+—-+————-+——-+——-+—————+—————+———+——+———-+——-+

1 row in set (0.05 sec)

mysql> insert into c2kw select c1,c2,c3,max(c4) from temp FORCE INDEX (ind_temp_c123) group by c1,c2,c3 ;

Query OK, 20000004 rows affected (2 min 0.85 sec)

Records: 20000004  Duplicates: 0  Warnings: 0

实际大约花费实际为:6 min

4、删除中间表

mysql> drop table temp;Query OK, 0 rows affected (0.99 sec)

实际大约花费实际为:1 sec

5、建立c索引

mysql> create index ind_c2kw_c1 on c2kw(c1);Query OK, 20000004 rows affected (49.74 sec)

Records: 20000004  Duplicates: 0  Warnings: 0

mysql> create index ind_c2kw_c2 on c2kw(c2);

Query OK, 20000004 rows affected (1 min 47.20 sec)

Records: 20000004  Duplicates: 0  Warnings: 0

mysql> create index ind_c2kw_c3 on c2kw(c3);

Query OK, 20000004 rows affected (2 min 42.02 sec)

Records: 20000004  Duplicates: 0  Warnings: 0

实际大约花费实际为:5分钟

6、清空a、b表

mysql> truncate table a2kw;Query OK, 0 rows affected (1.15 sec)

mysql> truncate table b2kw;

Query OK, 0 rows affected (1.34 sec)

实际大约花费实际为:3sec

一共花费的时间大概在15分钟左右

转自:http://blog.sina.com.cn/s/blog_5755b8ed0101err7.html

SQL语句基础

SQL基本语句:
SQL结构化查询语言的五大类:【请熟记英文】
1、select语句,select语句是数据库调优的重点部分
2、DML:数据处理语句【 data manipulation language 】 ==> 将会产生事务
主要有:insertdeleteupdatemerge
3、DDL:数据定义语句【 data define language 】
主要用于描述元数据,修改数据字典;
主要有:createalterdroptruncate
4、DCL:数据控制语句【 data control language 】
主要有:grantrevokeset
5、TCL:事务控制语句【transaction control language 】
主要有:commitrollbacksavepoint
二、DBA常用的管理工具
1、sqlplus
2、PL/SQL developer
3、toad
三、select语句的使用:
1、使用逗号分隔查询
2、分号结束查询
3、使用where子句过滤输出
4、一般使用外键进行join查询
四、select语句格式:
SELECT *|{DISTINCT} column|expression [alias],…} FROM table;
# 注解: | :表示可选参数;distinct:去除重复;alias:是对列使用别名
范例: SELECT * FROM emp;
五、演示 SQL查询语句与运算以及处理空行,处理重复的行

SQL> select salary from employees where employee_id=100;
SQL> select salary+100 from employees where employee_id=100;
SQL> select (salary+100)*2 from hr.employees where employee_id=100;

使用连接串

SQL> select last_name||job_id "Employees" from employees;
SQL> select last_name || ' is a ' || job_id "Employee Details" from employees;

desc语句用来查看表结构,实际上查询的是数据字典中的user_tab_columns的相应字段
SQL> desc employees;
关系型数据库中必须包含事务,数据字典和第三范式;第三方式可总结为列不可重复,列不可再分,行不可再分;