SQL

SQL

一条SQL搞定 插入数据主键重复或数据已经存在,则更新这条数据

数据库atool 发表了文章 • 0 个评论 • 443 次浏览 • 2016-04-22 14:20 • 来自相关话题

在做数据库开发的时候,经常会遇到这样的一种情景:

当一条数据不存在的时候,插入这条数据,如果这条数据的主键已经在数据库中存在,那么更新这条数据。

你们一般怎么做呢?先根据主键查询数据,然后判断是否存在数据,如果存在数据,则update字段,否则insert数据。

这样做的弊端就是需要两次连接数据库服务器,然后利用高级语言来判断是否存在的逻辑。

下面教你一条SQL语句,教你解决这类问题!

例如数据表weixin_user的表结构如下所示:(博客转移,图片丢失)
$sql = "insert into weixin_user(wx_id, wx_name, wx_state, wx_info, wx_lasttime) values ('$wx_id', '$wx_name', '$wx_state', '$wx_info', NOW()) ON DUPLICATE KEY UPDATE wx_name='$wx_name', wx_state = '$wx_state', wx_info = '$wx_info', wx_lasttime = NOW();";上面这段SQL语句,是本博客的微信和易信接口开发代码中的一条SQL语句(weixin_user用于记录微信易信粉丝的状态),下面图片中显示的就是这段代码,看到这个,你就懂了吧?

ON DUPLICATE KEY UPDATE(当出现DUPLICATE KEY主键重复错误的时候触发Update操作,当然要求就是表在设计的时候一定要有主键primary key)

具体的效果是什么?本微信号就在使用,你关注一下就知道了... 查看全部
在做数据库开发的时候,经常会遇到这样的一种情景:

当一条数据不存在的时候,插入这条数据,如果这条数据的主键已经在数据库中存在,那么更新这条数据。

你们一般怎么做呢?先根据主键查询数据,然后判断是否存在数据,如果存在数据,则update字段,否则insert数据。

这样做的弊端就是需要两次连接数据库服务器,然后利用高级语言来判断是否存在的逻辑。

下面教你一条SQL语句,教你解决这类问题!

例如数据表weixin_user的表结构如下所示:(博客转移,图片丢失)
$sql = "insert into weixin_user(wx_id, wx_name, wx_state, wx_info, wx_lasttime) values ('$wx_id', '$wx_name', '$wx_state', '$wx_info', NOW()) ON DUPLICATE KEY UPDATE wx_name='$wx_name', wx_state = '$wx_state', wx_info = '$wx_info', wx_lasttime = NOW();";
上面这段SQL语句,是本博客的微信和易信接口开发代码中的一条SQL语句(weixin_user用于记录微信易信粉丝的状态),下面图片中显示的就是这段代码,看到这个,你就懂了吧?

ON DUPLICATE KEY UPDATE(当出现DUPLICATE KEY主键重复错误的时候触发Update操作,当然要求就是表在设计的时候一定要有主键primary key)

具体的效果是什么?本微信号就在使用,你关注一下就知道了...

mysql数据库AUTO_INCREMENT 引起的id异常

数据库atool 发表了文章 • 0 个评论 • 497 次浏览 • 2016-04-22 14:13 • 来自相关话题

mysql设置id为自增属性,由于这个属性导致的一个bug。详细场景不描述了。

问题解决:

后来在MySQL官方找到对应的答案,原因是那天开新服时程序重启了数据库而暴露出这个BUG:

If you specify an AUTO_INCREMENT column for an InnoDBtable, the table handle in the InnoDB data dictionarycontains a special counter called the auto-increment counter that is used inassigning new values for the column. This counter is stored only in mainmemory, not on disk.

From:http://dev.mysql.com/doc/refma ... .html

简单来说,在InnoDB的表制定一个递增初始值时,innodb会为其维护一个auto-incrememt的计数器;但是这个计数器只保存在内存中,而不是在disk上。

而重启之后获得索引通过下面这种语法:

SELECTMAX(ai_col) FROM t FOR UPDATE;

获取到递增列的最大的值来开始递增,如果该表为空的话,那么就会从1开始递增。

【解决方案】

在创表时插入一个以1000开始的无效数据,确保重启mysql后新的递增初始值正常。同时查看其他表是否有类似问题,在后面涉及新建表时回归此类情况。 查看全部
mysql设置id为自增属性,由于这个属性导致的一个bug。详细场景不描述了。

问题解决:

后来在MySQL官方找到对应的答案,原因是那天开新服时程序重启了数据库而暴露出这个BUG:

If you specify an AUTO_INCREMENT column for an InnoDBtable, the table handle in the InnoDB data dictionarycontains a special counter called the auto-increment counter that is used inassigning new values for the column. This counter is stored only in mainmemory, not on disk.

From:http://dev.mysql.com/doc/refma ... .html

简单来说,在InnoDB的表制定一个递增初始值时,innodb会为其维护一个auto-incrememt的计数器;但是这个计数器只保存在内存中,而不是在disk上。

而重启之后获得索引通过下面这种语法:

SELECTMAX(ai_col) FROM t FOR UPDATE;

获取到递增列的最大的值来开始递增,如果该表为空的话,那么就会从1开始递增。

【解决方案】

在创表时插入一个以1000开始的无效数据,确保重启mysql后新的递增初始值正常。同时查看其他表是否有类似问题,在后面涉及新建表时回归此类情况。

使用in语句查询数据,并按照in里面的顺序排序

编程语言atool 发表了文章 • 0 个评论 • 307 次浏览 • 2016-04-22 11:19 • 来自相关话题

经常会用到用select * from tab where id in (1,2,3,4)语句。如果需要按照查询的id列表排列查询出来的数据记录的顺序该怎么做呢?
如下:
一、Accessselect * From 表 Where id in(1,5,3) order by instr(',1,5,3,',','&id&',')二、MSSQLselect * From 表 Where id in(1,5,3) order by charindex(','+rtrim(cast(id as varchar(10)))+',',',1,5,3,')三、MySql数据库
1.数字型in查询,db_node_id为intSELECT db_node_id, title FROM da_elements WHERE db_node_id IN (1342,1344,1343) ORDER BY FIELD( db_node_id, 1342,1344,1343 );2.字符型in查询,db_node_id为varcharSELECT db_node_id, title FROM da_elements WHERE db_node_id IN ('1342','1344','1343') ORDER BY FIELD( db_node_id, '1342','1344','1343' );Enjoy~ 查看全部
经常会用到用select * from tab where id in (1,2,3,4)语句。如果需要按照查询的id列表排列查询出来的数据记录的顺序该怎么做呢?
如下:
一、Access
select * From 表 Where id in(1,5,3) order by instr(',1,5,3,',','&id&',')
二、MSSQL
select * From 表 Where id in(1,5,3) order by charindex(','+rtrim(cast(id as varchar(10)))+',',',1,5,3,')
三、MySql数据库
1.数字型in查询,db_node_id为int
SELECT db_node_id, title FROM da_elements WHERE db_node_id IN (1342,1344,1343)  ORDER BY FIELD( db_node_id, 1342,1344,1343 );
2.字符型in查询,db_node_id为varchar
SELECT db_node_id, title FROM da_elements WHERE db_node_id IN ('1342','1344','1343')  ORDER BY FIELD( db_node_id, '1342','1344','1343' );
Enjoy~

一条SQL搞定 插入数据主键重复或数据已经存在,则更新这条数据

数据库atool 发表了文章 • 0 个评论 • 443 次浏览 • 2016-04-22 14:20 • 来自相关话题

在做数据库开发的时候,经常会遇到这样的一种情景:

当一条数据不存在的时候,插入这条数据,如果这条数据的主键已经在数据库中存在,那么更新这条数据。

你们一般怎么做呢?先根据主键查询数据,然后判断是否存在数据,如果存在数据,则update字段,否则insert数据。

这样做的弊端就是需要两次连接数据库服务器,然后利用高级语言来判断是否存在的逻辑。

下面教你一条SQL语句,教你解决这类问题!

例如数据表weixin_user的表结构如下所示:(博客转移,图片丢失)
$sql = "insert into weixin_user(wx_id, wx_name, wx_state, wx_info, wx_lasttime) values ('$wx_id', '$wx_name', '$wx_state', '$wx_info', NOW()) ON DUPLICATE KEY UPDATE wx_name='$wx_name', wx_state = '$wx_state', wx_info = '$wx_info', wx_lasttime = NOW();";上面这段SQL语句,是本博客的微信和易信接口开发代码中的一条SQL语句(weixin_user用于记录微信易信粉丝的状态),下面图片中显示的就是这段代码,看到这个,你就懂了吧?

ON DUPLICATE KEY UPDATE(当出现DUPLICATE KEY主键重复错误的时候触发Update操作,当然要求就是表在设计的时候一定要有主键primary key)

具体的效果是什么?本微信号就在使用,你关注一下就知道了... 查看全部
在做数据库开发的时候,经常会遇到这样的一种情景:

当一条数据不存在的时候,插入这条数据,如果这条数据的主键已经在数据库中存在,那么更新这条数据。

你们一般怎么做呢?先根据主键查询数据,然后判断是否存在数据,如果存在数据,则update字段,否则insert数据。

这样做的弊端就是需要两次连接数据库服务器,然后利用高级语言来判断是否存在的逻辑。

下面教你一条SQL语句,教你解决这类问题!

例如数据表weixin_user的表结构如下所示:(博客转移,图片丢失)
$sql = "insert into weixin_user(wx_id, wx_name, wx_state, wx_info, wx_lasttime) values ('$wx_id', '$wx_name', '$wx_state', '$wx_info', NOW()) ON DUPLICATE KEY UPDATE wx_name='$wx_name', wx_state = '$wx_state', wx_info = '$wx_info', wx_lasttime = NOW();";
上面这段SQL语句,是本博客的微信和易信接口开发代码中的一条SQL语句(weixin_user用于记录微信易信粉丝的状态),下面图片中显示的就是这段代码,看到这个,你就懂了吧?

ON DUPLICATE KEY UPDATE(当出现DUPLICATE KEY主键重复错误的时候触发Update操作,当然要求就是表在设计的时候一定要有主键primary key)

具体的效果是什么?本微信号就在使用,你关注一下就知道了...

mysql数据库AUTO_INCREMENT 引起的id异常

数据库atool 发表了文章 • 0 个评论 • 497 次浏览 • 2016-04-22 14:13 • 来自相关话题

mysql设置id为自增属性,由于这个属性导致的一个bug。详细场景不描述了。

问题解决:

后来在MySQL官方找到对应的答案,原因是那天开新服时程序重启了数据库而暴露出这个BUG:

If you specify an AUTO_INCREMENT column for an InnoDBtable, the table handle in the InnoDB data dictionarycontains a special counter called the auto-increment counter that is used inassigning new values for the column. This counter is stored only in mainmemory, not on disk.

From:http://dev.mysql.com/doc/refma ... .html

简单来说,在InnoDB的表制定一个递增初始值时,innodb会为其维护一个auto-incrememt的计数器;但是这个计数器只保存在内存中,而不是在disk上。

而重启之后获得索引通过下面这种语法:

SELECTMAX(ai_col) FROM t FOR UPDATE;

获取到递增列的最大的值来开始递增,如果该表为空的话,那么就会从1开始递增。

【解决方案】

在创表时插入一个以1000开始的无效数据,确保重启mysql后新的递增初始值正常。同时查看其他表是否有类似问题,在后面涉及新建表时回归此类情况。 查看全部
mysql设置id为自增属性,由于这个属性导致的一个bug。详细场景不描述了。

问题解决:

后来在MySQL官方找到对应的答案,原因是那天开新服时程序重启了数据库而暴露出这个BUG:

If you specify an AUTO_INCREMENT column for an InnoDBtable, the table handle in the InnoDB data dictionarycontains a special counter called the auto-increment counter that is used inassigning new values for the column. This counter is stored only in mainmemory, not on disk.

From:http://dev.mysql.com/doc/refma ... .html

简单来说,在InnoDB的表制定一个递增初始值时,innodb会为其维护一个auto-incrememt的计数器;但是这个计数器只保存在内存中,而不是在disk上。

而重启之后获得索引通过下面这种语法:

SELECTMAX(ai_col) FROM t FOR UPDATE;

获取到递增列的最大的值来开始递增,如果该表为空的话,那么就会从1开始递增。

【解决方案】

在创表时插入一个以1000开始的无效数据,确保重启mysql后新的递增初始值正常。同时查看其他表是否有类似问题,在后面涉及新建表时回归此类情况。

使用in语句查询数据,并按照in里面的顺序排序

编程语言atool 发表了文章 • 0 个评论 • 307 次浏览 • 2016-04-22 11:19 • 来自相关话题

经常会用到用select * from tab where id in (1,2,3,4)语句。如果需要按照查询的id列表排列查询出来的数据记录的顺序该怎么做呢?
如下:
一、Accessselect * From 表 Where id in(1,5,3) order by instr(',1,5,3,',','&id&',')二、MSSQLselect * From 表 Where id in(1,5,3) order by charindex(','+rtrim(cast(id as varchar(10)))+',',',1,5,3,')三、MySql数据库
1.数字型in查询,db_node_id为intSELECT db_node_id, title FROM da_elements WHERE db_node_id IN (1342,1344,1343) ORDER BY FIELD( db_node_id, 1342,1344,1343 );2.字符型in查询,db_node_id为varcharSELECT db_node_id, title FROM da_elements WHERE db_node_id IN ('1342','1344','1343') ORDER BY FIELD( db_node_id, '1342','1344','1343' );Enjoy~ 查看全部
经常会用到用select * from tab where id in (1,2,3,4)语句。如果需要按照查询的id列表排列查询出来的数据记录的顺序该怎么做呢?
如下:
一、Access
select * From 表 Where id in(1,5,3) order by instr(',1,5,3,',','&id&',')
二、MSSQL
select * From 表 Where id in(1,5,3) order by charindex(','+rtrim(cast(id as varchar(10)))+',',',1,5,3,')
三、MySql数据库
1.数字型in查询,db_node_id为int
SELECT db_node_id, title FROM da_elements WHERE db_node_id IN (1342,1344,1343)  ORDER BY FIELD( db_node_id, 1342,1344,1343 );
2.字符型in查询,db_node_id为varchar
SELECT db_node_id, title FROM da_elements WHERE db_node_id IN ('1342','1344','1343')  ORDER BY FIELD( db_node_id, '1342','1344','1343' );
Enjoy~