hit、run

mysql 批量插入数据如何防止重复插入

正式环境常用的数据备份和恢复的sql#数据备份sqlCREATE TABLE bs_house_cost_item_record_bak as SELECT * FROM bs_house_cost_item_record where community_id = 163;#删除原数据库中的数据DELETE FROM bs_house_cost_item_record where community_id = 163;#查询备份的数据SELECT * FROM bs_house_cost_i

INSERT INTO bs_sys_role_permission (role_id, permission_id, state, remark, version, create_time ) SELECT 33 as role_id,permission_id,state,remark,version,NOW()FROM bs_sys_role_permission WHERE role_id = 31;

统计每个楼栋表的房屋数量SELECT b.*, sum(case when (h.id is not null) then 1 else 0 end) as houseNumFROM bs_building b LEFT JOIN bs_house h ON b.id = h.building_id and h.deleted = 0 where b.deleted = 0 GROUP BY b.id

replace函数可以实现替换表中某个字段的一部分,变成新的一部分.并且保留其余部分.例如:表中的agents字段值原来为: abcd现在要把表中agents字段中的a都替换成f,既fbcd就可以使用replace函数实例: 当partner中的agents是oldAgentsPre时,将partner表中的oldAgentsPre替换成newAgentsPreUPDATE partner p SET p.`agents` = replace(p.`agents`,#{oldAgentsPre

mysql批量插入数据时,会存在重复数据的可能.可以通过1)数据库加联合索引2)修改sql,忽略重复的数据,防止数据库报错1.CREATE TABLE `merchantBlackList` ( `id` int(5) NOT NULL AUTO_INCREMENT, `createTime` datetime NOT NULL, `bankId` int(10) DEFAULT NULL COMMENT '银行id', `merchantSn` varchar(20) COLLA

DELETE u2FROM usersRelation u1, usersRelation u2WHERE u1.selfId = u2.selfIdAND u1.bossId = 263AND u2.bossId = 1

从左开始截取字符串SELECT LEFT('www.yuanrengu.com',8)从右开始截取字符串SELECT RIGHT('www.yuanrengu.com',6)

#查询每种手机型号的用户数,除了要根据手机型号分组外,还需要根据手机号去重SELECT mobileModel, COUNT(DISTINCT mobile) conFROM loginLogWHERE mobileModel IS NOT NULLGROUP BY mobileModel ORDER BY con desc ...

UPDATE users uINNER JOIN users r ON r.`id` = u.`referId`SET u.grade = (r.`grade` + 1)WHERE u.`grade` IS NULLAND r.`grade` IS NOT NULLAND u.`id` != 1

1.可以直接使用foreach标签,来实现批量更新,但是这种效率太低.就不写了2.还可以通过case when 语句来实现批量更新,这种写法效率更高,实际应用中一般使用此方法例如:<update id="updateBatch" parameterType="java.util.List"> update meetingSign <trim ...

假设有一个表(syslogs)有1000万条记录，需要在业务不停止的情况下删除其中statusid=1的所有记录，差不多有600万条， 直接执行 DELETE FROM syslogs WHERE statusid=1 会发现删除失败，因为lock wait timeout exceed的错误。 因为这条语句所涉及的记录数太多，因此我们通过LIMIT参数分批删除，比如每10...

group_concat就是拼接多行数据的函数select id,group_concat(re_id order by re_id separator ",") as re_id from tablenamegroup by id

参数说明:1) balanceKey:钱包余额加密字段;注意数据库不能设置为varchar类型,需要是varbinary(二进制)类型;例如:balanceKey varbinary(500) DEFAULT NULL;2) pskey是加密秘钥:项目中通过MD5加密方法获取例如:MD5Util.encoderWithSalt(str, salt);3) CAST AS 类型转换函数 ,...

#通过对表的主要字段,添加索引的方式.可以显著的提高查询效率ALTER TABLE t_jt_contract_integral_detail ADD INDEX index_userId (userId); ALTER TABLE t_jt_contract_integral_detail ADD INDEX index_comp (userId, contractId, staffId);

#1.删除表DROP TABLE IF EXISTS `t_jt_contract_birthday_present_delay_exchange_apply`;#2.创建表CREATE TABLE `t_jt_contract_birthday_present_delay_exchange_apply` ( `id` int(11) NOT NULL AUTO_INCREMENT COM

SELECT a.repayDateFROM t_jt_contract_data AS tLEFT JOIN (SELECT contractId, MAX(id) AS maxId FROM t_jt_contract_fund_ransom_apply GROUP BY contractId) AS c ON t.id = c.contractIdLEFT JOIN t_

mysql创建联合唯一索引

1:删除视图drop view if exists `v_t_birthday_gift_detail`;2.创建新视图CREATE or REPLACE VIEW `v_t_birthday_gift_detail` AS SELECT jcd.id, jcd.userName, jcd.contractTime as contractTime, jcd.userI

MySQL存储过程:包含输入和输出DELIMITER $$ DROP PROCEDURE IF EXISTS `p_integral_freeze_product_type_modify` $$ CREATE PROCEDURE p_integral_freeze_product_type_modify (IN `in_contractId` BIGINT ,IN `in_contractAp

DELIMITER $$DROP PROCEDURE IF EXISTS `pro_zlh_test`$$CREATE PROCEDURE pro_zlh_test ()BEGINDECLARE done INT DEFAULT 0 ;DECLARE _contractId BIGINT (20) ; #合同idDECLARE _contractIntegral INT ; #合同积分D

SELECT concat('update t_user t set t.username=', t.username, ' where t.id=', t.id, ';')FROM t_user tWHERE t.id = 1;

#删除t_jt_contract_user表中的重复数据,只保留id最小的DELETE FROM t_jt_contract_user WHERE userIDCard IN (SELECT a.userIDCard FROM(SELECT userIDCard FROM t_jt_contract_user GROUP BY userIDCard HAVING COUNT(userIDCa...

#导出表格语句SELECT CONVERT ((@rowNo :=@rowNo + 1), CHAR(10)) AS rowNo, a.*FROM ( date_format(t.investExpireDate, '%Y/%m/%d') AS investExpireDate, t.staffName, t.userPhone, (IFNULL(o.recivedInt...

SELECT DATE_FORMAT(t.`payDateTime`, "%Y-%m") month, COUNT(1) num, IFNULL(SUM(payMoney), 0) sumAmount FROM trade t <where> <if test=...