分类目录归档:数据库

mac osx通过homebrew安装postgresql

1. 安装

brew install postgresql

2. 初始化

initdb /usr/local/var/postgres -E utf8

如出现如下提示,代表已经初始化过了:

The files belonging to this database system will be owned by user "Ben".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

initdb: directory "/usr/local/var/postgres" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/usr/local/var/postgres" or run initdb
with an argument other than "/usr/local/var/postgres".

3. 启动运行

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
#启动
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

4. 创建用户权限

createuser -d -P postgres

5. 连接使用

postgresql

 

如能够正常连接,代表安装成功

“Data truncated for column”错误原因

一、字符长度太短;
二、乱码,更改统一的字符类型,比如更改字符类型为utf8;
三、如果是 Enum,则可能是添加的字符不在enum类型范围内;
四、另一可能是在alter table更改列设置时,影响原来存入的值,这时可将原值update为需要的类型值或删除这些原值再alter table。

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类型的字符串。

 

 

 

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  调整语句的执行计划,减少锁的申请数目。

按同一顺序访问对象:

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

避免事务中的用户交互:

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

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

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

使用脚底的隔离级别:

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

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

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

图解SQL的Join[转]

对于SQL的Join,在学习起来可能是比较乱的。我们知道,SQL的Join语法有很多inner的,有outer的,有left的,有时候,对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章(实在不清楚为什么Coding Horror也被墙)通过 文氏图 Venn diagrams 解释了SQL的Join。我觉得清楚易懂,转过来。

假设我们有两张表。

  • Table A 是左边的表。
  • Table B 是右边的表。

其各有四条记录,其中有两条记录是相同的,如下所示:

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

下面让我们来看看不同的Join会产生什么样的结果。

 

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

Inner join
产生的结果集中,是A和B的交集。

Venn diagram of SQL inner join
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Full outer join 产生A和B的并集。但是需要注意的是,对于没有匹配的记录,则会以null做为值。

Venn diagram of SQL cartesian join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Left outer join 产生表A的完全集,而B表中匹配的则有值,没有匹配的则以null值取代。

Venn diagram of SQL left join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null 

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

产生在A表中有而在B表中没有的集合。

join-left-outer.png
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null OR TableB.id IS null
id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

产生A表和B表都没有出现的数据集。

join-outer.png

还需要注册的是我们还有一个是“交差集” cross join, 这种Join没有办法用文式图表示,因为其就是把表A和表B的数据进行一个N*M的组合,即笛卡尔积。表达式如下:

SELECT * FROM TableA
CROSS JOIN TableB

这个笛卡尔乘积会产生 4 x 4 = 16 条记录,一般来说,我们很少用到这个语法。但是我们得小心,如果不是使用嵌套的select语句,一般系统都会产生笛卡尔乘积然再做过滤。这是对于性能来说是非常危险的,尤其是表很大的时候。

(全文完)

转自:http://coolshell.cn/articles/3463.html

树型结构之数据结构设计

我们经常需要在关系型数据库中保存一些树状结构数据,比如分类、菜单、论坛帖子树状回复等。常用的方法有两种:

1. 领接表的方式;


2. 预排序遍历树方式;

假设树状结构如下图:

领接表方式

主要依赖于一个 parent 字段,用于指向上级节点,将相邻的上下级节点连接起来,id 为自动递增自动,parent_id 为上级节点的 id。一目了然,“Java”是“Language”的子节点。

我们要显示树,PHP 代码也可以很直观,代码如下:

<?php
/**
 * 获取父节点下的所有子节点
 *
 * @since 2011-05-18
 * 
 * @param $parent_id 父节点 id,0 则显示整个树结构。
 * @param $level 当前节点所处的层级,用于缩进显示节点。
 * @return void
 */
function show_children ($parent_id = 0, $level = 0)
{
    // 获取父节点下的所有子节点
    $result = mysql_query('SELECT id, name FROM tree WHERE parent_id=' . intval($parent_id));
    // 显示每个子节点
    while ($row = mysql_fetch_array($result)) {
        // 缩进显示
        echo '<div style="margin-left:' . ($level * 12) . 'px">' . $row['name'] . '</div>';
        // 递归调用当前函数,显示再下一级的子节点
        show_children($row['id'], $level + 1);
    }
}
?>

想要显示整个树结构,调用 show_children()。想要显示“Database”子树,则调用 show_children(2),因为“Database”的 id 是 2。

继续阅读

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值的数据忽略掉了。