理解MySQL的CHAR和VARCHAR的区别

之前一直没认真研究过MySQL的CHAR和VARCHAR之间有什么区别,只是模糊的认为,CHAR比VARCHAR字符长度短一些。
查阅了一下MySQL的官方文档,有了一些认识。

原文内容如下:

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. See Section C.10.4, “Limits on Table Column Count and Row Size”.

其实翻译过来大意就是这样:

  1. 对于CHAR,字符长度在0-255之间,不足的长度将会以空格补齐,但在检索的时候,会删除补齐的空格。例如:char name(4),插入字符'''a'或者'abcd'都是占用4bytes。
  2. 对于VARCHAR,字符长度在0-65,535之间,有效的最大字符长度取决于此列最大行的长度,在各个列之间,最大有效长度取65,535 。同时,不同字符集也影响存储长度。其存储的内容不足长度不会以空格补齐。在符合标准的SQL中,尾部的空格在存储和检索时会被保留。例如:char name(4),插入字符'''a'或者'abcd',其长度分别为1,2,5byes 。

其次,如果未启用SQL严格模式,当CHAR或VARCHAR列超过列的最大长度,该值将被截​​断,以适应字段长度并产生一个警告。启用严格模式后,对于非空格字符截断时,会产生一个错误,并且不会真正插入值。

存储长度的差异如下图:

char-varchar-difference
char-varchar-difference

如果给定的值被存储到CHAR(4)和VARCHAR(4)列,从列中检索到的值并不总是相同,因为尾随空格从CHAR列检索时除去。例如:

char-varchar-spaces-select
char和varchar在检索时对于尾部空格的差异
char-varchar-spaces-like
LIKE检索方式会被尾部空格影响

所有MySQL的排序规则是PADSPACE类型的。这意味着所有CHAR,VARCHAR和TEXT值在MySQL中是不考虑任何尾随空格进行比较。
但是LIKE除外。
这些适用于所有的MySQL版本,而不受服务器的SQL模式。

官方文档原文地址:
https://dev.mysql.com/doc/refman/5.7/en/char.html

关于MySQL的FULLTEXT实现全文检索的注意事项

对于英文,MySQL的FULLTEXT属性来实现全文检索是相当方便且高效的,但是在使用过程中需要注意一些事项。

首先对我们对需要进行检索的字段添加FULLTEXT属性(假设已经建表):

[sql collapse=”false”]
alter table table_name add fulltext index(filed_1,filed_2);
[/sql]
接下来查询数据:
[sql collapse=”false”]
SELECT * FROM table_name WHERE MATCH (filed_1,filed_2) AGAINST (‘keyword’);
[/sql]
此处涉及到一个很重要的注意事项:

MySQL规定全文搜索中被搜索的单词所在的行数大于等于被搜索的所有行数的一半时候,就将被搜索单词作为Common word,即不被显示。(具体条件需要查阅资料确定)

因此,假设在测试的时候,表中只有一行数据,所以无论怎么执行上述查询语句,返回的结果总是为空。不必惊慌,多加几条没有待查关键词的数据就会有结果啦~

当然,MySQL提供了更加强大的查询结果过滤:
[sql collapse=”false”]
SELECT * FROM table_name WHERE MATCH (filed_1,filed_2) AGAINST (‘+keyword_1 -keyword_2’ IN BOOLEAN MODE);
[/sql]

这样,就会返回包含keyword_1的数据,而包含keyword_2的数据就会被过滤掉。

PHP serialize() VS unserialize()性能测试

最近遇到需要将一个复杂数组存储在MySQL的问题,找到两者方式:json_encode()和seriliaze().到底使用哪一个,于是想要测试一下两者的性能及使用时保持数据的结构完整性情况。
手册解释:

serialize — Generates a storable representation of a value

serialize — 产生一个可存储的值的表示

unserialize — Creates a PHP value from a stored representation

unserialize — 从已存储的表示中创建 PHP 的值

看这个解释还是有点陶醉,第一眼基本不是很明白,接下来在测试的过程中加深加理解。
测试使用环境:本机WAMP/PHP 5.5.12

一、初步测试json_encode/json_decode的效率与字符串长度,对一个简单的二位数组进行10000此循环测试。

1.测试代码如下:

[php collapse=”false”]
<?php

$array = array(
‘name’=>’luoning’,
‘sex’=>’man’,
‘age’=>21,
‘adress’=>’Lanzhou’,
‘phone’=>’110’,
‘other’=>array(
‘QQ’=>’947703573’,
‘Weibo’=>’Near_Je’,
‘blog’=>’http://www.luoning.me’
)
);

$json = json_encode($array);
$seri = serialize($array);

//输出json_encode/serialize之后的字符串长度
echo "json :" , strlen($json) ,'<br/>’;
echo "serialize :", strlen($seri) ,'<br/>’;

//—–测试json_encode/json_decode开始—
$stime = microtime(true);

for ($i = 0; $i < 10000; $i ++) {
json_encode($array);
}

$etime = microtime(true);
echo "json_encode :", ($etime – $stime) ,'<br/>’;

//———————————
$stime = microtime(true);

for ($i = 0; $i < 10000; $i ++) {
json_decode($json,true);
}

$etime = microtime(true);

echo "json_decode :", ($etime – $stime),'<br/>’;

//——测试serialize/unserialize开始—–
$stime = microtime(true);

for ($i = 0; $i < 10000; $i ++) {
serialize($array);
}

$etime = microtime(true);

echo "serialize :", ($etime – $stime) ,'<br/>’;

//——————————-
$stime = microtime(true);

for ($i = 0; $i < 10000; $i ++) {
unserialize($seri);
}

$etime = microtime(true);

echo "unserialize :", ($etime – $stime),'<br/>’;
[/php]

2.测试结果:

json :150
serialize :224
json_encode :0.12014198303223
json_decode :0.23659014701843
serialize :0.10482311248779
unserialize :0.12682294845581

3.结果分析

(1)两者比较,字符串的长度json_encode具有优势;
(2)经过多次刷新测试,数据变化幅度不大,在循环10000次的条件下serialize函数均比json_decode所需时间短,但是优势很小,综合比较还是相差无几。

二、接下来测试一下一个较大且比较复杂的数组的情况下二者性能差别以及数据恢复之后的完整性等。
具体数组不再展示,流程基本一样。
1.测试结果:

json :11431
serialize :15748
json_encode :2.82439494133
json_decode :6.316869020462
serialize :4.2260599136353
unserialize :3.5953121185303

2.结果分析:

(1)当数组变大之后,json_encode依旧有字符串长度小的优势;
(2)json_encode过程挽回时间上的落差,基本只需要serialize一半;
(3)json_decode相比unserialize又是的两倍的时间,势均力敌!
(4)以上多次刷新测试数据变化幅度不大。

3.其他测试项目:

(1)json_decode/unserialize之后的数组与原数组相同,结构完整。(没有测试更复杂情况)

相比较来说,在我看来两者的性能是差不多的,基本持平。
至于选用哪一个,可根据以上数据具体分析选用,但是考虑到跨语言等方面,还是推荐json_encode,毕竟现在很多API都是json数据传输的。
4.2015-04-17更新

经过一些实际使用对比,发现json式序列化在存入数据后之后读取之时会出现json_decode后为null的情况,百度原因为BOM,由于我的数据比较复杂,没有用去除BOM的方式解决,只好换回serialize了,问题立马fixed!

 

MySQL远程连接授权

在阿里云ECS上部署了LNMP环境,由于MySQL默认了只有localhost才能链接访问,所以必须先授权才能远程使用Navicat等客户端工具链接。
  • 改表法

    可能是你的帐号不允许从远程登陆,只能在localhost。这个时候只要在localhost的那台电脑使用mysql管理工具登入mysql后,更改 “mysql” 数据库里的 “user” 表里的 “host” 项,从”localhost”改称”%”.
    修改完成命令行执行> flush privileges;即可立即生效。

  • 授权法

    A:你想mysql账户myuser使用密码mypassword从任何主机连接到mysql服务器的话,那就在mySQL命令行下输入:
    GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;若上面那条命令还没有奏效,那就使用下面的命令,一定成功!

    如果你想允许想mysql账户myuser从ip为192.168.1.3的主机连接到mysql服务器,并使用mypassword作为密码,那就在mySQL命令行下输入:
    GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;