mysql 执行语句小知识

✌ 摘要

• 新的一年换了一家正规团队
  需要将开发过程中涉及到的数据库结构变化
  在版本迭代上线前，统一整理给 DBA 运维人员
  所以，便会接触到更多的 sql 语句，在此做下技能小备忘，欢迎品鉴 …

✍ 小技能积累

• 新增字段（包含注释）

#新增 decimal 类型字段
ALTER TABLE mz_tab_test ADD cost_count decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '任务成本';

#新增 tinyint 类型字段
ALTER TABLE mz_tab_test ADD income_tag tinyint(2) NOT NULL DEFAULT '0' COMMENT '收入维护标记 0：未维护，1：已维护';

• 修改字段 （包含注释）

ALTER TABLE mz_tab_test modify column  `url` varchar(120) NOT NULL DEFAULT '' COMMENT '链接'

• 新增索引

#新增唯一索引
ALTER TABLE mz_tab_test ADD UNIQUE `idx_task_id` (`task_id` ) COMMENT 'task_id 唯一索引';

#新增多个唯一索引
ALTER TABLE mz_tab_test ADD UNIQUE `idx_item_name_manager_id` (`item_name`, `manager_id` ) COMMENT 'idx_item_name_manager_id 唯一索引';

• 删除索引

# 索引名在前 ，表名在后
drop index idx_item_name on mz_tab_test ;

• 创建新表，语句举例：(注意去掉 AUTO_INCREMENT )

CREATE TABLE `mz_tab_brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `brand_name` varchar(20) NOT NULL DEFAULT '' COMMENT '品牌方名称',
  `status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态 1：启用，2：禁用',
  `createtime` int(10) NOT NULL DEFAULT '0' COMMENT '添加时间',
  `updatetime` int(10) NOT NULL DEFAULT '0' COMMENT '更新时间',
  `admin_id` int(10) NOT NULL DEFAULT '0' COMMENT '操作管理员ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='品牌方数据表';

• 删除重复数据，保留最新一条

delete from mz_tab_test where id not in (
        select t.max_id from
        (select max(id) as max_id from mz_tab_test group by task_id) as t
);

• 查询所有的索引

show INDEX from <表名> ;

---

▷ Laravel 更新原字段

• 如果更新时间字段，多一秒（处理冗余报错）

// 更新模型，同时使用 DB::raw 来设置 updated_at 字段
$model->update([
    'column_name' => 'new_value', // 你想更新的其他字段
    'updated_at' => DB::raw('DATE_ADD(NOW(), INTERVAL 1 SECOND)'), // 设置 updated_at 为当前时间加1秒
]);

• 更新原字段的基础上 +1，+10

// 假设我们需要更新 users 表，将名字为 'John' 的用户的 score 字段增加 10
DB::table('users')
  ->where('name', 'John')
  ->update([
      'score' => DB::raw('score + 10')
  ]);

---

▷ Laravel 事务处理中，出现更新死锁问题

• 报错信息如下：
  
• 所得经验如下( 参考文章 >>>)：

无论前台后台的程序，都不应该存在仅根据非主键的几个字段一查就要 update/delete 的场景。
即使有，也应该改为先把要更新的记录查出来然后逐条按主键 id 更新

所得处理如下

//中心思想就是，更新是的查询条件，与查询时的不要一样，尽量使用主键进行更新操作
$arrPayLogIds = Paylog::where($unpaidFilter)->pluck('paylogid');
$_update = ['status' => 0,'desc' => '批量作废'];
Paylog::whereIn('paylogid',$arrPayLogIds)->update($_update);

---

▷ mysql 一个表数据转移到另外一个表的方法

• 如果在同一个服务器上，新表不存在那可以直接创建新表并复制一份数据

	create table table2name select * from db1.tablename1;

• 如果表已经存在，可以直接用如下语句

	insert into table2name select * from db1.tablename1;

参考： mysql一个表数据转移到另外一个表的2种方法4种情况

---

▷ 提取一个表中的字段，插入到另一张表中

insert into table_target(id,create_time) select id,createtime from table_other;

参考： mysql 将一个表的数据导入到另一个表

---

▷ 数据库操作

• 1. 查询指定数据库中，所有数据表的主键

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'b2b' AND COLUMN_KEY = 'PRI';

• 2. 查询指定数据库中，有多少数据表

SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'b2b';

附录