标签归档:mysql

Field ‘XXX’ doesn’t have a default value问题解决方法

MySQL 5中,出现错误提示:
Field 'id' doesn't have a default value

解决方法一:

打开my.ini,查找
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改为

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后重启MYSQL

解决方法二:
MySQL 5 uses a strict mode which needs to be disabled.
In Windows, Goto Start-->Programs-->MySQL->MySQL Instance Config Wizard. Follow through the Reconfigure Instance option-->Detailed Configuration-->Continue Next a few screens. At the bottom under Enable TCP/IP option there is 'Enable Strict Mode'. Deslect this option (no tick). Save changes and MySQL will restart

MySQL CAST与CONVERT 函数的用法

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值,并产生另一个类型的值。两者具体的语法如下:

CAST(value as type);
CONVERT(value, type);

就是CAST(xxx AS 类型), CONVERT(xxx,类型)。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个:

  • 二进制,同带binary前缀的效果 : BINARY
  • 字符型,可带参数 : CHAR()
  • 日期 : DATE
  • 时间: TIME
  • 日期时间型 : DATETIME
  • 浮点数 : DECIMAL
  • 整数 : SIGNED
  • 无符号整数 : UNSIGNED

下面举几个例子:

例一

mysql> SELECT CONVERT('23',SIGNED);
+----------------------+
| CONVERT('23',SIGNED) |
+----------------------+
|                   23 |
+----------------------+
1 row in set

例二

mysql> SELECT CAST('125e342.83' AS signed);
+------------------------------+
| CAST('125e342.83' AS signed) |
+------------------------------+
|                          125 |
+------------------------------+
1 row in set

例三

mysql> SELECT CAST('3.35' AS signed);
+------------------------+
| CAST('3.35' AS signed) |
+------------------------+
|                      3 |
+------------------------+
1 row in set

像上面例子一样,将varchar 转为int 用 cast(a as signed),其中a为varchar类型的字符串。

 

 

 

手动编辑安装PHP扩展

开发和部署的过程中可能会经常出现需要额外安装PHP扩展的情况,下边以PDO_MYSQL为例,介绍下手动编译安装PHP扩展:

先到http://pecl.php.net/找需要的版本,我用的是稳定的版本。要先看看说明,特别是要注意mysql的php的版本。

wget http://pecl.php.net/get/PDO_MYSQL-1.0.2.tgz
tar xzvf PDO_MYSQL-1.0.2.tgz
cd PDO_MYSQL-1.0.2
/usr/local/php/bin/phpize
Configuring for:
PHP Api Version: 20041225
Zend Module Api No: 20060613
Zend Extension Api No: 220060519
./configure --with-php-config=/usr/local/php/bin/php-config

经过configure就可以make了

make
make install

注意pdo_mysql的全路径,我的是:

/usr/local/php/lib/php/extensions/debug-non-zts-20060613/pdo_mysql.so

然后在/usr/local/lib/php.ini

加上一句:

extension=/usr/local/php/lib/php/extensions/debug-non-zts-20060613/pdo_mysql.so

重新启动apache即可看到已经加载pdo_mysql成功。

MySQL数据库阻塞与死锁间的区别

        数据库阻塞和死锁在程序开发过程经常出现,怎么样避免呢?下面通过Demo简单模拟下,数据库发生阻塞和死锁的现象

一、数据库阻塞:
数据库阻塞的现象:第一个连接占有资源没有释放,而第二个连接需要获取这个资源。如果第一个连接没有提交或者回滚,第二个连接会一直等待下去,直到第一个连接释放该资源为止。对于阻塞,数据库无法处理,所以对数据库操作要及时地提交或者回滚。

实例:

--创建表
create table tb(id int,createtime date);
--插入测试数据
insert into tb select 1,sysdate from dual;
insert into tb select 2,sysdate from dual;
insert into tb select 3,sysdate from dual;
commit;

第一个连接,不提交或者回滚:

update tb set id=2 where id=1;

1 row updated

第二个连接,一直在运行:

update tb set id=2 where id=1;

因为第一个连接占有tb表没有释放资源,而第二个连接一直在等待第一个连接释放该资源。

二、数据库死锁:
数据库死锁的现象:第一个连接占有资源没有释放,准备获取第二个连接所占用的资源,而第二个连接占有资源没有释放,准备获取第一个连接所占用的资源。这种互相占有对方需要获取的资源的现象叫做死锁。对于死锁,数据库处理方法:牺牲一个连接,保证另外一个连接成功执行。

实例:

--创建测试表t1
create table t1(id int,createtime date);
insert into t1 select 1,sysdate from dual;
insert into t1 select 2,sysdate from dual;
insert into t1 select 3,sysdate from dual;
commit;

--创建测试表t2
create table t2(id int,createtime date);
insert into t2 select 1,sysdate from dual;
insert into t2 select 2,sysdate from dual;
insert into t2 select 3,sysdate from dual;
commit;

第一个连接,在command窗口中运行:

begin
--先修改t1
update t1 set id=2
where id=1;
--等待20s
dbms_lock.sleep(20);
--再修改t2
update t2 set id=2
where id=1;
end;

运行结果:
ORA-00060: 等待资源时检测到死锁
ORA-06512: 在 line 9

第二个连接:

begin
--先修改t2
update t2 set id=2
where id=1;
--等待20s
dbms_lock.sleep(20);
--再修改t1
update t1 set id=2
where id=1;
end;

运行结果:
PL/SQL procedure successfully completed

因为第一个连接占有表t1,想要获取表t2的资源,而第二个连接占有表t2,想要获取表t1的资源,这种互相占有对方想要获取的资源,满足死锁现象。最后第一个连接报异常退出,而第二个连接执行成功。

------------------------------------------------------

死锁所在的资源和检测:
在SQL Server的两个或多个任务中,如果某个任务锁定了其他任务试图锁定的资源。会造成这些任务的永久阻塞,从而出现死锁。

下图为例:

l  事务T1获得了行R1的共享锁。

l  事务T2获得了行R2的共享锁。

l  然后事务T1请求行R2的排它锁,但是T2完成并释放其对R2的共享锁之前被阻塞。

l  T2请求行R1的排它锁,但是事务T1完成并释放其对R1持有的共享锁之前被阻塞。

现在T2与T1相互等待,导致了死锁。一般情况下监视器会自动检测并解决这个问题。

 


可以发生死锁的资源:
死锁不仅仅发生在锁资源上面,还会发生在一下资源上:

l  锁。例如页、行、元数据和应用程序上的锁。

l  工作线程。如果排队等待线程的任务拥有阻塞所有其他工作线程的资源,也会导致死锁。

l  内存。当并发请求等待获得内存,而当前的可用内存无法满足其需要时,可能发生死锁。

l  并行查询执行的相关资源。当一个语句用到多个线程运行时,线程之间有可能发生死锁。

死锁检测:
默认5秒钟搜索SQL Server中的所有任务,检测是否有死锁。如果有,将选择一个作为牺牲品,并返回1205错误。一般是开销最小的事务作为牺牲品。

死锁与阻塞的差别:

阻塞:当一个事务请求一个被其他事务锁定的资源上的锁时,发出请求的事务会一直等待下去,知道该锁被别人释放,自己能申请到位置。

默认情况下除非设置了LOCK_TIMEOUT,否则事务会一直等待下去。

死锁:两个或多个进程之间的相互等待。但是由于SQL Server有数据库引擎死锁检测方案,至少5秒钟会消除一个现有的死锁。对性能的影响往往没有阻塞严重。

问题定位:
1、 跟踪标志1204和跟踪标志1222:

打开跟踪的语句:

DBCC TRACEON(1222,-1)

DBCC TRACEON(1204,-1)


对于1222产生的结果解释:

1、 死锁牺牲的进程:第一句deadlockvictim=processXXXX,中的xxxx就是死锁牺牲品。

2、 死锁发生的进程信息:第二部分的process-list


3、 发生死锁的资源信息:在结果的resource-list中


4、 死锁图形事件:

从sqlserver profiler中得到,一般结合1222跟踪标志和sql trace。

首先从errorlog中寻找1222的输出结果,根据输出的时间在跟踪里找到相应的连接。然后分析原因。

www.it165.net

解决办法:
尽管死锁不能完全避免,但是可以把机会降到最低:

l  按同一顺序访问对象。

l  避免事务中的用户交互。

l  保持事务简短并处于一个批处理中。

l  使用脚底的隔离级别。

l  调整语句的执行计划,减少锁的申请数目。

按同一顺序访问对象:

如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。

避免事务中的用户交互:

避免编写包含用户交互的事务,因为没有用户干预的批处理的运行速度远快于必须等待用户响应时的查询速度。

保持事务简短并处于一个批处理中:

运行时间越长,等待时间就越长,造成死锁的机会就越高。

使用脚底的隔离级别:

确定事务能否在低隔离级别上运行。尽可能使用较低的隔离级别。

调整语句的执行计划,减少锁的申请数目:

可以从执行计划中找出哪些资源耗得比较多。此时锁的数目也会相应增多。

mysql 双向同步的键值冲突问题的解决方法

我们只要保证两台服务器上插入的自增长数据不同就可以了
如:A查奇数ID,B插偶数ID,当然如果服务器多的话,你可以定义算法,只要不同就可以了

在这里我们在A,B上加入参数,以实现奇偶插入

A:my.cnf上加入参数

auto_increment_offset = 1
auto_increment_increment = 2
这样A的auto_increment字段产生的数值是:1, 3, 5, 7, …等奇数ID了

B:my.cnf上加入参数

auto_increment_offset = 2
auto_increment_increment = 2
这样B的auto_increment字段产生的数值是:2, 4, 6, 8, …等偶数ID了

启动多个mysql 实例

1、  创建多个mysql database目录

Mysql_install_db  --datadir=/data/mysql_3307

2、  设置database目录权限

Chown –R mysql /data

3、  拷贝、设置my.cnf

cp  XXX/my.cnf /data/mysql_3307/my.cnf

添加my.cnf

log-error = /data/mysql_3307/localhost.localdomain.err
pid-file = /data/mysql_3307/localhost.localdomain.pid

4、  添加启动脚本:

#!/bin/ti
rundir=`pwd`
datadir=$rundir
cnf=$rundir"/my.cnf"

echo "server path : $rundir"
echo "data path : $datadir"
echo "cnf : $cnf"

/usr/local/mysql/bin/mysqld_safe /
--defaults-file="$cnf" /
--pid-file="$rundir"/mysql.pid /
--datadir="$datadir" /
--socket="$rundir"/mysql.sock /
&

5、  OK,chmod a+x my.cnf,./my.cnf,启动

6、Ok,启动正常,如上图所示。

可能遇到的问题:

1)/usr/local/mysql/libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)

原因是没有执行第2步,给数据库数据文件夹设置用户组权限;

2)Can't find messagefile '/data/mysql_3307/share/mysql/english/errmsg.sys'

需要在my.cnf中配置语言的路径,添加如下:language = /usr/local/mysql/share/mysql/English

3)mysql 连接不上mysql server

注意mysql 指明配置文件的路径,例如mysql –defaults-file=/data/mysql_3307/my.cnf 。在my.cnf文件中需要检查client.socket的文件是否正确。

两种方式建立Vsftpd虚拟用户

我们登录FTP有三种方式:

  • 匿名登录;
  • 本地用户登录;
  • 虚拟用户登录;

匿名登录:在登录FTP时使用默认的用户名,一般是ftp或anonymous。
本地用户登录:使用系统用户登录,在/etc/passwd中。
虚拟用户登录:这是FTP专有用户,有两种方式实现虚拟用户,本地数据文件和数据库服务器。
FTP虚拟用户是FTP服务器的专有用户,使用虚拟用户登录FTP,只能访问FTP服务器提供的资源,大大增强了系统的安全。

本文实验的Linux系统是CentOS 5

一、本地数据文件方式
1. 添加虚拟用户口令文件

vi /etc/vsftpd/vftpuser.txt

添加虚拟用户名和密码,一行用户名,一行密码,以此类推。奇数行为用户名,偶数行为密码。

将刚添加的vftpuser.txt虚拟用户口令文件转换成系统识别的口令认证文件。
首先查看系统有没有安装生成口令认证文件所需的软件db4-utils。

rpm –qa |grep db4-utils
rpm –ivh db4-utils-4.3.29-9.fc6.i386.rpm

下面使用db_load命令生成虚拟用户口令认证文件。

db_load –T –t hash –f /etc/vsftpd/vftpuser.txt /etc/vsftpd/vftpuser.db

3. 编辑vsftpd的PAM认证文件
在/etc/pam.d目录下,

vi /etc/pam.d/vsftpd

将里面其他的都注释掉,添加下面这两行:

auth required /lib/security/pam_userdb.so db=/etc/vsftpd/vftpuser
account required /lib/security/pam_userdb.so db=/etc/vsftpd/vftpuser

4. 建立本地映射用户并设置宿主目录权限
所有的FTP虚拟用户需要使用一个系统用户,这个系统用户不需要密码。

useradd –d /home/vftpsite –s /sbin/nologin vftpuser
chmod 700 /home/vftpsite

5. 配置vsftpd.conf(设置虚拟用户配置项)

vi /etc/vsftpd/vsftpd.conf
guest_enable=YES #开启虚拟用户
guest_username=vftpuser #FTP虚拟用户对应的系统用户
pam_service_name=vsftpd #PAM认证文件

6. 重启vsftpd服务

service vsftpd restart

7. 根据设置的账号和密码测试FTP链接。

二、数据库服务器(MySQL)方式
1. 安装MySQL

2. 建立本地映射用户并设置宿主目录权限

useradd –d /home/vftpsite –s /sbin/nologin vftpuser
chmod 700 /home/vftpsite

3. 配置vsftpd.conf(设置虚拟用户配置项)

vi /etc/vsftpd/vsftpd.conf
guest_enable=YES #开启虚拟用户
guest_username=vftpuser #FTP虚拟用户对应的系统用户
pam_service_name=vsftpd #PAM认证文件

4. 在MySQL中建立用户口令数据库

#mysql –u root –p
mysql> create database vftpuser; #建立虚拟用户数据库,库名vftpuser
mysql> use vftpuser; #进入vftpuser数据库

mysql> create table users(name char(16) binary,passwd char(16) binary); #建立虚拟用户口令表,表名users

mysql> insert into users (name,passwd) values ('bobyuan',password('111'));
mysql> insert into users (name,passwd) values ('markwang',password('111'));

#建立两个虚拟用户,bobyuan和markwang
注:在这里我用这种方法添加的虚拟用户密码都是经过MySQL加密的,加密后的密码pam-mysql不能识别(MySQL和pam-mysql兼容性有些问题),因此本次实验使用明文保存密码

添加明文密码:
方法一:单个添加用户

mysql> insert into users (name,passwd) values ('bobyuan', '111');
mysql> insert into users (name,passwd) values ('markwang',‘111');

方法二:批量添加用户
新建vftpuser.txt文件

#vi vftpuser.txt

添加用户名和密码,注意字段数据之间要用Tab键隔开。
bobyuan 111
markwang 111

mysql>use vftpuser;
mysql>load data local infile “/vftpuser.txt”into table users;
mysql>flush privileges;
mysql> grant select on vftpuser.users to vftpuser@localhost identified by '111111'; #授权vftpuser这个账号可以读取vftpuser数据库的user表

5. 验证第4步的设置是否成功

[root@CentOS5 /]#mysql –u vftpuser –p
mysql>show databases;
mysql>use vftpuser;
mysql>show tables;
mysql>select * from users;
mysql>quit

如下图:
6. 编译MySQL的PAM认证模块
查看/lib/security目录下有没有MySQL对应的PAM模块。

如果没有则下载pam-mysql安装(http://sourceforge.net/projects/pam-mysql),
我下载的是pam_mysql-0.7RC1.tar.gz。
[root@CentOS5 /]#cd /usr/local/src
[root@CentOS5 src]#tar –zxvf pam_mysql-0.7RC1.tar.gz
[root@CentOS5 src]#cd pam_mysql-0.7RC1
[root@CentOS5 pam_mysql-0.7RC1]# ./configure --with-mysql=/usr/local/mysql/ --with-pam-mods-dir=/lib/security/
[root@CentOS5 pam_mysql-0.7RC1]#make
[root@CentOS5 pam_mysql-0.7RC1]#make install

7. 编辑vsftpd的PAM认证文件
在/etc/pam.d目录下,

[root@CentOS5 /]#vi /etc/pam.d/vsftpd
将里面其他的都注释掉,添加下面这两行:
auth required pam_mysql.so user=vftpuser passwd=111111 host=localhost db=vftpuser table=users usercolumn=name passwdcolumn=passwd crypt=0
account required pam_mysql.so user=vftpuser passwd=111111 host=localhost db=vftpuser table=users usercolumn=name passwdcolumn=passwd crypt=0
crypt=0:表示口令使用明文方式保存在数据库中
crypt=1:表示口令使用UNIX的DES加密方式加密后保存在数据库中
crypt=2:表示口令使用MySQL的password()函数加密后保存在数据库中
crypt=3:表示口令使用MD5散列值的方式保存在数据库中

8. 重启vsftpd服务
[root@CentOS5 /]#service vsftpd restart

9. 测试虚拟用户登录FTP

三、虚拟用户高级设置
1. virtual_use_local_privs参数

当virtual_use_local_privs=YES时,虚拟用户和本地用户有相同的权限;
当virtual_use_local_privs=NO时,虚拟用户和匿名用户有相同的权限,默认是NO。

当virtual_use_local_privs=YES,write_enable=YES时,虚拟用户具有写权限(上传、下载、删除、重命名)。

当virtual_use_local_privs=NO,write_enable=YES,anon_world_readable_only=YES,
anon_upload_enable=YES时,虚拟用户不能浏览目录,只能上传文件,无其他权限。

当virtual_use_local_privs=NO,write_enable=YES,anon_world_readable_only=NO,
anon_upload_enable=NO时,虚拟用户只能下载文件,无其他权限。

当virtual_use_local_privs=NO,write_enable=YES,anon_world_readable_only=NO,
anon_upload_enable=YES时,虚拟用户只能上传和下载文件,无其他权限。

当virtual_use_local_privs=NO,write_enable=YES,anon_world_readable_only=NO,
anon_mkdir_write_enable=YES时,虚拟用户只能下载文件和创建文件夹,无其他权限。

当virtual_use_local_privs=NO,write_enable=YES,anon_world_readable_only=NO,
anon_other_write_enable=YES时,虚拟用户只能下载、删除和重命名文件,无其他权限。

2. 建立各个虚拟用户自身的配置文件

[root@CentOS5 /]#vi /etc/vsftpd/vsftpd.conf
添加:
user_config_dir=/etc/vsftpd/vsftpd_user_conf
[root@CentOS5 /]#mkdir /etc/vsftpd/vsftpd_user_conf
编辑bobyuan的配置文件
[root@CentOS5 /]#vi /etc/vsftpd/vsftpd_user_conf/bobyuan
添加:
anon_world_readable_only=NO #开放bobyuan的下载权限(只能下载)。注意这个地方千万不能写成YES,否则bobyuan将不能列出文件和目录。
编辑markwang的配置文件
[root@CentOS5 /]#vi /etc/vsftpd/vsftpd_user_conf/markwang
添加:
write_enable=YES #开放markwang的写权限
anon_world_readable_only=NO #开放markwang的下载权限
anon_upload_enable=YES #开放markwang的上传权限
anon_mkdir_write_enable=YES #开放markwang创建目录的权限
anon_other_write_enable=YES #开放markwang删除和重命名的权限

四、虚拟用户配置文件(实验)
1. 所有虚拟用户使用统一配置

[root@CentOS5 /]#vi /etc/vsftpd/vsftpd.conf
write_enable=YES
anonymous_enable=NO
local_enable=YES
guest_enable=YES
guest_username=vftpuser
virtual_use_local_privs=NO
pam_service_name=vsftpd
anon_world_readable_only=NO #可以下载
anon_upload_enable=NO(默认值) #不能上传
anon_mkdir_write_enable=NO(默认值) #不能新建文件夹
anon_other_write_enable=NO(默认值) #不能删除和重命名文件
ftpd_banner=Welcome to BOB FTP server
xferlog_enable=YES
xferlog_file=/var/log/vsftpd.log
xferlog_std_format=YES
ascii_upload_enable=YES
ascii_download_enable=YES
tcp_wrappers=NO
setproctitle_enable=YES
listen_port=21
connect_from_port_20=YES
idle_session_timeout=600
data_connection_timeout=120
#vi /etc/vsftpd/vsftpd.conf
write_enable=YES
anonymous_enable=NO
local_enable=YES
guest_enable=YES
guest_username=vftpuser
virtual_use_local_privs=NO
pam_service_name=vsftpd
user_config_dir=/etc/vsftpd/vsftpd_user_conf
ftpd_banner=Welcome to BOB FTP server
xferlog_enable=YES
xferlog_file=/var/log/vsftpd.log
xferlog_std_format=YES
ascii_upload_enable=YES
ascii_download_enable=YES
tcp_wrappers=NO
setproctitle_enable=YES
listen_port=21
connect_from_port_20=YES
idle_session_timeout=600
data_connection_timeout=120
max_clients=0
max_per_ip=3
local_max_rate=512000
[root@CentOS5 /]#mkdir /etc/vsftpd/vsftpd_user_conf
编辑bobyuan(FTP匿名用户)的配置文件
[root@CentOS5 /]#vi /etc/vsftpd/vsftpd_user_conf/bobyuan
anon_world_readable_only=NO
编辑ftpadmin(FTP匿名管理员)的配置文件
#vi /etc/vsftpd/vsftpd_user_conf/ftpadmin
anon_world_readable_only=NO
anon_upload_enable=YES
anon_mkdir_write_enable=YES
anon_other_write_enable=YES
# setsebool allow_ftpd_full_access 1
# setsebool allow_ftpd_use_cifs 1
# setsebool allow_ftpd_use_nfs 1
# setsebool ftp_home_dir 1
# setsebool httpd_enable_ftp_server 1
# setsebool tftp_anon_write 1
# service vsftpd restart
Shutting down vsftpd: [ OK ]
Starting vsftpd for vsftpd: [ OK ]
# getsebool -a|grep ftp
allow_ftpd_anon_write --> off
allow_ftpd_full_access --> on
allow_ftpd_use_cifs --> on
allow_ftpd_use_nfs --> on
ftp_home_dir --> on
httpd_enable_ftp_server --> on
tftp_anon_write --> on

listen=YES
listen_port=21
tcp_wrappers=YES //支持tcp_wrappers,限制访问(/etc/hosts.allow,/etc/hosts.deny)
listen=YES的意思是使用standalone启动vsftpd,而不是super daemon(xinetd)控制它 (vsftpd推荐使用standalone方式)
anonymous_enable=NO
local_enable=YES //PAM方式此处必须为YES,如果不是将出现如下错误:
500 OOPS: vsftpd: both local and anonymous access disabled!
write_enable=NO
anon_upload_enable=NO
anon_mkdir_write_enable=NO
anon_other_write_enable=NO
chroot_local_user=YES
guest_enable=YES
guest_username=vsftpd //这两行的意思是采用虚拟用户形式
virtual_use_local_privs=YES //虚拟用户和本地用户权限相同

MySQL数据库中CHAR与VARCHAR之争[转]

在数据库中,字符型的数据是最多的,可以占到整个数据库的80%以上。为此正确处理字符型的数据,对于提高数据库的性能有很大的作用。在字符型数据中,用的最多的就是Char与Varchar两种类型。前面的是固定长度,而后面的是可变长度。现在我们需要考虑的是,在什么情况下使用Char字符型数据,什么情况下采用Varchar字符型数据。在这部分内容中,我就跟大家来探讨一下这个话题。

MySQL数据库中CHAR与VARCHAR之争

一、VARCHAR与CHAR字符型数据的差异

在MySQL数据库中,用的最多的字符型数据类型就是Varchar和Char.。这两种数据类型虽然都是用来存放字符型数据,但是无论从结构还是从数据的保存方式来看,两者相差很大。而且其具体的实现方式,还依赖与存储引擎。我这里就以大家最常用的MYISAM存储引擎为例,谈谈这两种数据类型的差异。在后续建议中,也是针对这种存储类型而言的。

这里首先需要明白的一点是,这两种数据类型,无论采用哪一种存储引起,系统存储数据的方式都是不同的。正是因为如此,我们才有必要研究两者的不同。然后在合适的情况下,采用恰当的方式。了解这一点之后,我们再来看后续的内容。

Varchar往往用来保存可变长度的字符串。简单的说,我们只是给其固定了一个最大值,然后系统会根据实际存储的数据量来分配合适的存储空间。为此相比CHAR字符数据而言,其能够比固定长度类型占用更少的存储空间。不过在实际工作中,由于某系特殊的原因,会在这里设置例外。如管理员可以根据需要指定ROW_FORMAT=FIXED选项。利用这个选项来创建MyISAM表的话,系统将会为每一行使用固定长度的空间。此时会造成存储空间的损耗。通常情况下,VARCHAR数据类型能够节约磁盘空间,为此往往认为其能够提升数据库的性能。不过这里需要注意的是,这往往是一把双刃剑。其在提升性能的同时,往往也会产生一些副作用。如因为其长度是可变的,为此在数据进行更新时可能会导致一些额外的工作。如在更改前,其字符长度是10位(Varchar规定的最长字符数假设是50位),此时系统就只给其分配10个存储的位置(假设不考虑系统自身的开销)。更改后,其数据量达到了20位。由于没有超过最大50位的限制,为此数据库还是允许其存储的。只是其原先的存储位置已经无法满足其存储的需求。此时系统就需要进行额外的操作。如根据存储引擎不同,有的会采用拆分机制,而有的则会采用分页机制。

CHAR数据类型与VARCHAR数据类型不同,其采用的是固定长度的存储方式。简单的说,就是系统总为其分配最大的存储空间。当数据保存时,即使其没有达到最大的长度,系统也会为其分配这么多的存储空间。显然,这种存储方式会造成磁盘空间的浪费。这里笔者需要提醒的一点是,当字符位数不足时,系统并不会采用空格来填充。相反,如果在保存CHAR值的时候,如果其后面有空值,系统还会自动过滤其空格。而在进行数据比较时,系统又会将空格填充到字符串的末尾。

显然,VARCHAR与CHAR两种字符型数据类型相比,最大的差异就是前者是可变长度,而后者则是固定长度。在存储时,前者会根据实际存储的数据来分配最终的存储空间。而后者则不管实际存储数据的长度,都是根据CHAR规定的长度来分配存储空间。这是否意味着CHAR的数据类型劣于VARCHAR呢?其实不然。否则的话,就没有必要存在CHAR字符类型了。虽然VARCHAR数据类型可以节省存储空间,提高数据处理的效率。但是其可变长度带来的一些负面效应,有时候会抵消其带来的优势。为此在某些情况下,还是需要使用Char数据类型。

二、项目建议

根据上面的分析,我们知道VARCHAR数据类型是一把双刃剑,其在带来性能提升的同时,也可能会存在着一些额外的消耗。我们在评估到底是使用VARCHAR数据类型还是采用CHAR数据类型时,就需要进行均衡。在实际项目中,我们会考量如下情况。

一是根据字符的长度来判断。如某个字段,像人的名字,其最长的长度也是有限的。如我们给其分配18个字符长度即可。此时虽然每个人的名字长度有可能不同,但是即使为其分配了固定长度的字符类型,即18个字符长度,最后浪费的空间也不是很大。而如果采用NVARCHAR数据类型时,万一以后需要改名,而原先的存储空间不足用来容纳新的值,反而会造成一些额外的工作。在这种情况下,进行均衡时,会认为采用CHAR固定长度的数据类型更好。在实际项目中,如果某个字段的字符长度比较短此时一般是采用固定字符长度。

二是考虑其长度的是否相近。如果某个字段其长度虽然比较长,但是其长度总是近似的,如一般在90个到100个字符之间,甚至是相同的长度。此时比较适合采用CHAR字符类型。比较典型的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时,就非常使用采用CHAR字符类型。因为其长度是相同的。另外,像用来存储用户的身份证号码等等,一般也建议使用CHAR类型的数据。

另外请大家考虑一个问题,CHAR(1)与VARCHAR(1)两这个定义,会有什么区别呢?虽然这两个都只能够用来保存单个的字符,但是VARCHAR要比CHAR多占用一个存储位置。这主要是因为使用VARCHAR数据类型时,会多用1个字节用来存储长度信息。这个管理上的开销CHAR字符类型是没有的。

三是从碎片角度进行考虑。使用CHAR字符型时,由于存储空间都是一次性分配的。为此某个字段的内容,其都是存储在一起的。单从这个角度来讲,其不存在碎片的困扰。而可变长度的字符数据类型,其存储的长度是可变的。当其更改前后数据长度不一致时,就不可避免的会出现碎片的问题。故使用可变长度的字符型数据时,数据库管理员要时不时的对碎片进行整理。如执行数据库导出导入作业,来消除碎片。

四是即使使用Varchar数据类型,也不能够太过于慷慨。这是什么意思呢?如现在用户需要存储一个地址信息。根据评估,只要使用100个字符就可以了。但是有些数据库管理员会认为,反正Varchar数据类型是根据实际的需要来分配长度的。还不如给其大一点的呢。为此他们可能会为这个字段一次性分配200个字符的存储空间。这VARCHAR(100)与VARCHAR(200)真的相同吗?结果是否定的。虽然他们用来存储90个字符的数据,其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说,硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的,但是对于内存来说,则不是。其时使用固定大小的内存块来保存值。简单的说,就是使用字符类型中定义的长度,即200个字符空间。显然,这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。所以如果某些字段会涉及到文件排序或者基于磁盘的临时表时,分配VARCHAR数据类型时仍然不能够太过于慷慨。还是要评估实际需要的长度,然后选择一个最长的字段来设置字符长度。如果为了考虑冗余,可以留10%左右的字符长度。千万不能认为其为根据实际长度来分配存储空间,而随意的分配长度,或者说干脆使用最大的字符长度。

MySQL处理空值时需要注意的两个问题

MySQL数据库是一个基于结构化数据的开源数据库。SQL语句是MySQL数据库中核心语言。不过在MySQL数据库中执行SQL语句,需要小心两个陷阱。

陷阱一:空值不一定为空

空值是一个比较特殊的字段。在MySQL数据库中,在不同的情形下,空值往往代表不同的含义。这是MySQL数据库的一种特性。如在普通的字段中(字符型的数据),空值就是表示空值。但是如果将一个空值的数据插入到TimesTamp类型的字段中,空值就不一定为空。此时为出现什么情况呢(如下图)?

MySQL中处理空值时要小心两个个陷阱

我先创建了一个表。在这个表中有两个字段:User_id(其数据类型是int)、Date(其数据类型是TimesTamp)。现在往这个表中插入一条记录,其中往Date字段中插入的是一个NULL空值。可是当我们查询时,其结果显示的却是插入记录的当前时间。这是怎么一回事呢?其实这就是在MySQL数据库中执行SQL语句时经常会遇到的一个陷阱:空值不一定为空。在操作时,明明插入的是一个空值的数据,但是最后查询得到的却不是一个空值。

在MySQL数据库中,NULL对于一些特殊类型的列来说,其代表了一种特殊的含义,而不仅仅是一个空值。对于这些特殊类型的列,各位读者主要是要记住两个。一个就是笔者上面举的TimesTamp数据类型。如果往这个数据类型的列中插入Null值,则其代表的就是系统的当前时间。另外一个是具有auto_increment属性的列。如果往这属性的列中插入Null值的话,则系统会插入一个正整数序列。而如果在其他数据类型中,如字符型数据的列中插入Null的数据,则其插入的就是一个空值。

陷阱二:空值不一定等于空字符

在MySQL中,空值(Null)与空字符(’’)相同吗?答案是否定的。请大家先来看下图的演示。

MySQL中处理空值时要小心两个个陷阱

在同一个数据库表中,同时插入一个Null值的数据和一个’’空字符的数据,然后利用Select语句进行查询。最后显示的结果如上图所示。显然其显示的结果是不相同的。从这个结果中就可以看出,空值不等于空字符。这就是在MySQL中执行SQL语句遇到的第二个陷阱。在实际工作中,空值数据与空字符往往表示不同的含义。数据库管理员可以根据实际的需要来进行选择。如对于电话号码等字段,可以默认设置为空值(表示根本不知道对方的电话号码)或者设置为空字符(表示后来取消了这个号码)等等。由于他们在数据库中会有不同的表现形式,所以数据库管理员需要区别对待。笔者更加喜欢使用空值,而不是空字符。这主要是因为针对空值这个数据类型有几个比较特殊的运算字符。如果某个字段是空字符,数据库中是利用字段名称来代替。相反,如果插入的是空值,则直接显示的是NULL。这跟其他数据库的显示方式也是不同的。

一是IS NULL 和IS NOT NULL关键字。如果要判断某个字段是否含用空值的数据,需要使用特殊的关键字。其中前者表示这个字段为空,后者表示这个字段为非空。在Select语句的查询条件中这两个关键字非常的有用。如需要查询所有电话号码为空的用户(需要他们补充电话号码信息),就可以在查询条件中加入is not null关键字。

MySQL中处理空值时要小心两个个陷阱

二是Count等统计函数,在空值上也有特殊的应用。如现在需要统计用户信息表中有电话号码的用户数量,此时就可以使用count函数、同时将电话号码作为参数来使用。因为在统计过程中,这个函数会自动忽略空值的数据。此时统计出来的就是有电话号码的用户信息。如果采用的是空字符的数据,则这个函数会将其统计进去。如下图所示,统计刚才建立的两条记录时,系统统计的结果是1,而不是2。可见系统自动将Null值的数据忽略掉了。