AIGC智能提示词项目实践(1)：深入MySQL高级语法，提升开发效率

AIGC智能提示词项目实践-1：深入MySQL高级语法，提升开发效率

- 1.读取数据表中的字段进行脱敏(*加密)
- 2.自动获取对应的数据表和字段
- 3.表单有数据才进行更新的条件语句(构成数组)
- 4.动态更新字段且进行条件判断
- 5.动态更新数据表和字段
- 6.字段自身+1的操作
- 7.多关键词查询，如“，”、“ ”、“|”分割关键词
- 8.如何安装格式和顺序生成需要的数组
- 9.批量插入并更新语句的生成
- 10.指定列和全部列的读取方式
- 11.批量分流将Excle中的数据插入数据库
- 12.合并字段
- 13根据特定条件更新数据库中的记录
- 14.将json对象插入数据表

在这个特别的日子里，我们迎来了1024程序员节，一个属于所有代码创造者和数字建筑师的庆典。1024，这个对程序员来说充满魔力的数字，不仅因为它代表了2的十次方，是二进制计数系统中一个重要的里程碑，也象征着每一位程序员用智慧与汗水构建起的科技世界的基础单元。正如每一个1024字节组合成庞大的数据海洋，程序员们通过一行行代码编织出丰富多彩的应用程序和服务，为我们的生活带来了前所未有的便捷。

值此佳节之际，让我们一同探索如何运用AIGC（人工智能生成内容）来辅助提升开发效率，特别是深入MySQL高级语法的学习和应用。随着技术的发展，数据库管理系统的使用已经变得越来越普遍，而掌握其高级特性对于优化查询性能、确保数据安全以及简化日常维护任务至关重要。在接下来的内容里，我们将一起探讨几个实用的MySQL高级语法技巧，帮助大家更高效地完成项目开发，并且能够更加自如地应对各种复杂的业务场景。

现在，请跟随我进入MySQL高级语法的世界，让我们的编程之旅更加精彩纷呈。无论是想要提高现有项目的运行效率，还是希望在新的项目中打下坚实的技术基础，今天分享的知识点都将为你提供有力的支持。祝所有的程序员朋友们1024节日快乐，愿你在编码的路上不断进步，享受创作的乐趣！

1.读取数据表中的字段进行脱敏(*加密)

提示词：mysql读取字段，对后8为使用*加密，如何写语句？

SELECT CASE WHEN phone_number IS NULL OR phone_number = '' THEN NULLWHEN LENGTH(phone_number) <= 8 THEN REPEAT('*', LENGTH(phone_number))ELSE CONCAT(LEFT(phone_number, LENGTH(phone_number) - 8), REPEAT('*', 8))END AS masked_phone_number
FROM users;

2.自动获取对应的数据表和字段

如何根据 $up_types和$ up_cate参数的不同，自动获取对应的数据表和字段，进行更新操作？

// 更新数量$table = $up_types == 0 ? $db->table('divide_poi') : $db->table('inspect_poi');$field = $up_cate == 0 ? 'poi_door_flag' : 'poi_photo_flag';$sql = "UPDATE {$table} SET {$field} = {$field} + 1 WHERE poi_id = {$from_id}";$db->query($sql);

特点说明：

减少重复代码：三个查询逻辑相同，只有up_cate参数不同。
提高可读性：通过函数封装来简化代码。
安全性：确保SQL语句是安全的，避免SQL注入。

3.表单有数据才进行更新的条件语句(构成数组)

如果poi_group、poi_sales_rep_id、poi_inspector、poi_important_notes为空时，则不更新对应的字段，如何调整语句

$poi_id = $dataArr['poi_id'];
$updates = [];// 检查每个字段是否非空，并添加到更新列表中
if (!empty($dataArr['poi_group'])) {$updates[] = "poi_group = '" . $db->escape($dataArr['poi_group']) . "'";
}
if (!empty($dataArr['poi_sales_rep_id'])) {$updates[] = "poi_sales_rep_id = '" . $db->escape($dataArr['poi_sales_rep_id']) . "'";
}
if (!empty($dataArr['poi_inspector'])) {$updates[] = "poi_inspector = '" . $db->escape($dataArr['poi_inspector']) . "'";
}
if (!empty($dataArr['poi_important_notes'])) {$updates[] = "poi_important_notes = '" . $db->escape($dataArr['poi_important_notes']) . "'";
}// 如果有需要更新的字段
if (count($updates) > 0) {// 构造最终的SQL语句$sql = "UPDATE " . $db->table('divide_poi') . " SET " . implode(', ', $updates) . " WHERE poi_id " . create_in($poi_id);// 执行查询$db->query($sql);
} else {// 如果没有需要更新的字段，则可以跳过更新操作或记录一条消息echo "没有需要更新的数据。";
}

1.创建了一个空数组$updates用于存储所有将要更新的字段。
2.对于每一个可能的更新字段，使用!empty()函数检查它是否非空（即不为null、空字符串或只包含空白字符）。如果字段非空，就将其加入到$updates数组中。
3.检查$updates数组是否含有任何元素。如果有，就生成并执行相应的UPDATE SQL语句；如果没有，则表明没有任何字段需要更新。

4.动态更新字段且进行条件判断

提示词：数据表中某字段，如果减1操作，如果会小于0，则将该字段设置为0，使用SQL中的GREATEST函数

UPDATE {$table}
SET {$field} = GREATEST({$field} - 1, 0)
WHERE poi_id = {$poi_id};

尝试将{ $KaTeX parse error: Expected 'EOF', got '}' at position 6: field}̲的值减1，但如果减1后的结果小\dots$ field}设置为0。GREATEST函数在这里用来保证{$field}的最终值至少为0。

5.动态更新数据表和字段

提示词：使用传统的 if-elseif-else 结构来选择正确的字段，动态更新指定的数据表和字段

// 根据 $poi_types 的值选择正确的表
$table = $poi_types == 0 ? 'divide_poi' : 'inspect_poi';// 根据 $poi_flag 的值选择正确的字段
if ($poi_flag == 0) {$field = 'poi_door_flag';
} elseif ($poi_flag == 1) {$field = 'poi_photo_flag';
} elseif ($poi_flag == 2) {$field = 'poi_video_flag';
} else {throw new InvalidArgumentException('Invalid poi_flag value: ' . $poi_flag);
}// 构建并执行更新语句
$sql = "UPDATE {$table} SET {$field} = {$field} - 1 WHERE poi_id = ?";

6.字段自身+1的操作

提示词：不读取数据库的情况下，更新指定字段+1

UPDATE your_table_name
SET poi_door_flag = poi_door_flag + 1
WHERE some_condition;

7.多关键词查询，如“，”、“ ”、“|”分割关键词

提示词：在mysql查询语句中，如if ($poi_sub_region != “”) {
$sql .= " AND poi_sub_region like ‘%" . $poi_sub_region . "%’";
}，如果poi_sub_region有徐州北区,徐州区域逗号隔开的关键词，如何进行多关键词查询，代码如何升级

$keywords = explode(',', $poi_sub_region); // 假设 $poi_sub_region 是 "徐州北区,徐州区域"
$sql = "SELECT * FROM your_table WHERE 1=1"; // 初始化SQL语句// 创建一个由关键词组成的正则表达式
$regex = implode('|', array_map('preg_quote', array_map('trim', $keywords)));if (!empty($regex)) {$sql .= " AND poi_sub_region REGEXP '" . $regex . "'";
}

array_map(‘trim’, $keywords)用于移除每个关键词两边可能存在的空格，array_map(‘preg_quote’, …)用于转义任何特殊字符，这样它们就会被当作普通文本处理，而implode(‘|’, …)则是用来构建正则表达式的模式，其中|表示“或”的意思。

8.如何安装格式和顺序生成需要的数组

提示词：将sensor_name,sensor_alias,sensor_unit_type,sensor_type,sensor_address_number,
sensor_register_address,sensor_realtime_data,sensor_sort_order,sensor_upload_formula,sensor_download_formula,sensor_configuration_info,
sensor_group_id,sensor_device_category,sensor_indicator_category,sensor_ip_address,sensor_change_filter_value,sensor_data_precision, sensor_storage_policy,sensor_area,sensor_location,sensor_switch_record,sensor_total_height,sensor_max_value,sensor_min_value,sensor_data_confirmation_count依次生成 $data = [
‘sensor_name’ => $sensor_name,
‘sensor_alias’ => $sensor_alias
]

$data = ['sensor_name' => $sensor_name,'sensor_alias' => $sensor_alias,'sensor_unit_type' => $sensor_unit_type,'sensor_type' => $sensor_type,'sensor_address_number' => $sensor_address_number,'sensor_register_address' => $sensor_register_address,'sensor_realtime_data' => $sensor_realtime_data,'sensor_sort_order' => $sensor_sort_order,'sensor_upload_formula' => $sensor_upload_formula,'sensor_download_formula' => $sensor_download_formula,'sensor_configuration_info' => $sensor_configuration_info,'sensor_group_id' => $sensor_group_id,'sensor_device_category' => $sensor_device_category,'sensor_indicator_category' => $sensor_indicator_category,'sensor_ip_address' => $sensor_ip_address,'sensor_change_filter_value' => $sensor_change_filter_value,'sensor_data_precision' => $sensor_data_precision,'sensor_storage_policy' => $sensor_storage_policy,'sensor_area' => $sensor_area,'sensor_location' => $sensor_location,'sensor_switch_record' => $sensor_switch_record,'sensor_total_height' => $sensor_total_height,'sensor_max_value' => $sensor_max_value,'sensor_min_value' => $sensor_min_value,'sensor_data_confirmation_count' => $sensor_data_confirmation_count
];

9.批量插入并更新语句的生成

提示词：将poi_video_flag,poi_photo_flag,poi_position_flag,poi_param_id,poi_editor,poi_edit_time，依次按poi_important_notes=VALUES(poi_important_notes)格式生成字符串，并用,分割。

poi_video_flag=VALUES(poi_video_flag),poi_photo_flag=VALUES(poi_photo_flag),poi_position_flag=VALUES(poi_position_flag),poi_param_id=VALUES(poi_param_id),poi_editor=VALUES(poi_editor),poi_edit_time=VALUES(poi_edit_time)

10.指定列和全部列的读取方式

提示词:想要从某个特定的表中读取数据，并且同时显示格式化的日期字段，如何写语句？

SELECT *, DATE_FORMAT(your_date_column, '%Y-%m') AS formatted_date
FROM your_table_name;

11.批量分流将Excle中的数据插入数据库

global $db;
dbc();// 增加PHP执行时间和内存限制
ini_set('max_execution_time', 300); // 设置最大执行时间为300秒
ini_set('memory_limit', '512M');    // 设置最大内存限制为512MB$batchSize = 2000; // 每批处理的记录数
$sqlBatch = [];
$sqlTemplate = 'INSERT INTO ' . $db->table('divide_poi') . ' (poi_id,poi_month,poi_province,poi_region,poi_sub_region,poi_township_name,poi_sales_rep_id,poi_inspector,poi_group,poi_status,poi_sub_status,poi_remarks,poi_customer_id,poi_label_name,poi_address_reference,poi_longitude,poi_latitude,poi_matched_address,poi_level,poi_project,poi_temp_project,poi_number_survey,poi_reissue_or_activate,poi_focus,poi_contact_person,poi_contact_phone,poi_channel,poi_links_check,poi_filing_record,poi_cooperation_type,poi_red_channel,poi_red_score,poi_distribution_score,poi_display_score,poi_fridge_score,poi_channel_score,poi_is_qualified,poi_ko_fridge_count,poi_other_fridge_count,poi_important_notes) VALUES ';for ($j = 2; $j <= $highestRow; $j++) {// 获取当前行的所有字段值$poi_data = [$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue() . "-01",$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("L" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("M" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("N" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("O" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("P" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("Q" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("R" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("S" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("T" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("U" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("V" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("W" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("X" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("Y" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("Z" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AA" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AB" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AC" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AD" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AE" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AF" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AG" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AH" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AI" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AJ" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AK" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AL" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AM" . $j)->getValue(),$objPHPExcel->getActiveSheet()->getCell("AN" . $j)->getValue()];// 构建单个插入项if (!empty($poi_data[1])) { // 假设poi_month是必填项// 对每个值进行适当的转义和格式化$itemStr = '(' . implode(',', array_map(function($value) {if (is_numeric($value)) {return $value; // 如果是数字，则不加引号} else {return "'" . addslashes($value) . "'"; // 如果是字符串，则加引号并转义}}, $poi_data)) . '),';$sqlBatch[] = $itemStr;}// 如果达到批次大小，则执行批量插入if (count($sqlBatch) >= $batchSize) {$sql = $sqlTemplate . implode('', $sqlBatch);$sql = rtrim($sql, ',') . " ON DUPLICATE KEY UPDATE poi_month=VALUES(poi_month),poi_province=VALUES(poi_province),poi_region=VALUES(poi_region),poi_sub_region=VALUES(poi_sub_region),poi_township_name=VALUES(poi_township_name),poi_sales_rep_id=VALUES(poi_sales_rep_id),poi_inspector=VALUES(poi_inspector),poi_group=VALUES(poi_group),poi_status=VALUES(poi_status),poi_sub_status=VALUES(poi_sub_status),poi_remarks=VALUES(poi_remarks),poi_customer_id=VALUES(poi_customer_id),poi_label_name=VALUES(poi_label_name),poi_address_reference=VALUES(poi_address_reference),poi_longitude=VALUES(poi_longitude),poi_latitude=VALUES(poi_latitude),poi_matched_address=VALUES(poi_matched_address),poi_level=VALUES(poi_level),poi_project=VALUES(poi_project),poi_temp_project=VALUES(poi_temp_project),poi_number_survey=VALUES(poi_number_survey),poi_reissue_or_activate=VALUES(poi_reissue_or_activate),poi_focus=VALUES(poi_focus),poi_contact_person=VALUES(poi_contact_person),poi_contact_phone=VALUES(poi_contact_phone),poi_channel=VALUES(poi_channel),poi_links_check=VALUES(poi_links_check),poi_filing_record=VALUES(poi_filing_record),poi_cooperation_type=VALUES(poi_cooperation_type),poi_red_channel=VALUES(poi_red_channel),poi_red_score=VALUES(poi_red_score),poi_distribution_score=VALUES(poi_distribution_score),poi_display_score=VALUES(poi_display_score),poi_fridge_score=VALUES(poi_fridge_score),poi_channel_score=VALUES(poi_channel_score),poi_is_qualified=VALUES(poi_is_qualified),poi_ko_fridge_count=VALUES(poi_ko_fridge_count),poi_other_fridge_count=VALUES(poi_other_fridge_count),poi_important_notes=VALUES(poi_important_notes)";$db->query($sql);// 加入短暂的延迟sleep(1); // 例如暂停1秒$sqlBatch = []; // 清空批次数组}
}// 处理剩余的数据
if (count($sqlBatch) > 0) {$sql = $sqlTemplate . implode('', $sqlBatch);$sql = rtrim($sql, ',') . " ON DUPLICATE KEY UPDATE poi_month=VALUES(poi_month),poi_province=VALUES(poi_province),poi_region=VALUES(poi_region),poi_sub_region=VALUES(poi_sub_region),poi_township_name=VALUES(poi_township_name),poi_sales_rep_id=VALUES(poi_sales_rep_id),poi_inspector=VALUES(poi_inspector),poi_group=VALUES(poi_group),poi_status=VALUES(poi_status),poi_sub_status=VALUES(poi_sub_status),poi_remarks=VALUES(poi_remarks),poi_customer_id=VALUES(poi_customer_id),poi_label_name=VALUES(poi_label_name),poi_address_reference=VALUES(poi_address_reference),poi_longitude=VALUES(poi_longitude),poi_latitude=VALUES(poi_latitude),poi_matched_address=VALUES(poi_matched_address),poi_level=VALUES(poi_level),poi_project=VALUES(poi_project),poi_temp_project=VALUES(poi_temp_project),poi_number_survey=VALUES(poi_number_survey),poi_reissue_or_activate=VALUES(poi_reissue_or_activate),poi_focus=VALUES(poi_focus),poi_contact_person=VALUES(poi_contact_person),poi_contact_phone=VALUES(poi_contact_phone),poi_channel=VALUES(poi_channel),poi_links_check=VALUES(poi_links_check),poi_filing_record=VALUES(poi_filing_record),poi_cooperation_type=VALUES(poi_cooperation_type),poi_red_channel=VALUES(poi_red_channel),poi_red_score=VALUES(poi_red_score),poi_distribution_score=VALUES(poi_distribution_score),poi_display_score=VALUES(poi_display_score),poi_fridge_score=VALUES(poi_fridge_score),poi_channel_score=VALUES(poi_channel_score),poi_is_qualified=VALUES(poi_is_qualified),poi_ko_fridge_count=VALUES(poi_ko_fridge_count),poi_other_fridge_count=VALUES(poi_other_fridge_count),poi_important_notes=VALUES(poi_important_notes)";$db->query($sql);
}

优化要点：

增加PHP执行时间和内存限制：通过ini_set函数增加了脚本的最大执行时间和内存限制。
加入延迟：在每次处理完一个批次后，使用sleep(1)暂停1秒钟，以避免服务器过载。
错误处理：虽然没有显式地添加错误处理，但你可以考虑在 $d b - > q u ery ($ sql)之后添加错误处理逻辑，以便更好地调试和记录问题。

12.合并字段

提示词：$sql = "SELECT sensor_ref,sensor_name,sensor_alias”，如何将sensor_name,sensor_alias合并成一个字段

SELECT sensor_ref,CONCAT(sensor_name, ', ', sensor_alias) AS sensor_full_name
FROM your_table_name;

13根据特定条件更新数据库中的记录

减少数据库调用次数：如果$highestRow的值很大，那么这段代码将执行大量的数据库更新操作。
考虑批量更新来减少与数据库的交互次数。
提高可读性：通过使用更具描述性的变量名和适当注释来提高代码的可读性。
异常处理：增加适当的错误处理机制，以确保在出现问题时能够妥善处理。
性能优化：比如检查是否真的需要每次循环都获取当前活动的工作表，或者是否可以通过预加载某些数据来加快处理速度。

$updates = [];
$params = [];for ($j = 2; $j <= $highestRow; $j++) {$sensor_group_id = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue(); // 分组ID$sensor_name = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue(); // 分组名称$sensor_address_number = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue(); // 地址号&位置// 假设$sensor_name和$sensor_location应该是不同的值$sensor_location = $objPHPExcel->getActiveSheet()->getCell("某个单元格")->getValue(); // 应该提供正确的单元格引用$updates[] = "WHEN :address{$j} THEN :name{$j}";$updates[] = "WHEN :address{$j}_loc THEN :location{$j}";$updates[] = "WHEN :address{$j}_grp THEN :group{$j}";// 存储参数值$params[":address{$j}"] = $params[":address{$j}_loc"] = $params[":address{$j}_grp"] = $sensor_address_number;$params[":name{$j}"] = $sensor_name;$params[":location{$j}"] = $sensor_location; // 使用正确的变量$params[":group{$j}"] = $sensor_group_id;
}if (!empty($updates)) {$sql = "UPDATE template_sensorSET sensor_name = (CASE sensor_address_number " . implode(' ', $updates) . " END),sensor_location = (CASE sensor_address_number " . implode(' ', array_slice($updates, 1, null, true)) . " END),sensor_group_id = (CASE sensor_address_number " . implode(' ', array_slice($updates, 2, null, true)) . " END)WHERE member_id = :member_id AND cate_id = :cate_id AND type_id = :type_idAND sensor_address_number IN (" . implode(', ', array_unique(array_keys($params))) . ")";// 添加额外的参数$params[':member_id'] = $member_id;$params[':cate_id'] = $cate_id;$params[':type_id'] = $type_id;// 执行SQL语句try {$stmt = $db->prepare($sql);$stmt->execute($params);} catch (PDOException $e) {// 处理异常error_log($e->getMessage());}
}

14.将json对象插入数据表

提示词：将’{“fcode”:“03”,“type”:“uint”,“readType”:“0”}'当做字符串插入数据表

-- 假设 data_field 是 JSON 类型
INSERT INTO your_table (data_field) VALUES ('{"fcode":"03","type":"uint","readType":"0"}');
-- 或者
INSERT INTO your_table (data_field) VALUES (JSON_OBJECT('fcode', '03', 'type', 'uint', 'readType', '0'));