Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

In modern project management and operation and maintenance work, using large models (such as ChatGPT) to handle complex tasks has become an efficient means. Recently, in a project, we tried to use a large model to migrate SQL statements exported from MySQL to the DAMO database format. After several rounds of operations, we deeply realized the importance of prompt word engineering and summarized the best practices of using large models.

Background of the project

In the project, we need to migrate a SQL file exported from MySQL to the DAMO database format. The specific requirement is to add double quotes to all table names and field names in the SQL statement. Manual processing is not only time-consuming and error-prone, so we decided to try to use a large model to complete this task.

Operation process

First round of operations:

We upload the desensitized SQL file to ChatGPT and give the command "help me add double quotes to the SQL statement table name and list in the file". The operation is shown in the figure below:

Preliminary results show that most statements are processed correctly, but some are not executed as required. As shown in the following figure:

At this point, we realize the importance of prompt words and examples.

Second round of operation:

For the first round of results, we clearly pointed out the problems and provided a correct example after manual processing.

Although the results have improved, some overly long SQL statements are still not processed correctly. As shown in the following figure:

At this point, we further adjusted the prompt words and enhanced the coverage of the examples.

The third round of operation:

Based on the first two rounds, we increased the number of examples and designed more detailed prompts, including clear requirements, correct SQL statement examples, and expected results after the operation.

The complete "prompt word project" is as follows:

帮我把文件中的sql语句表名和列名加上双引号。如下为表名和列名加上双引号的正确示例，请将如上文件进行修改后，并提供下载：INSERT INTO "QRTZ_CRON_TRIGGERS" ("SCHED_NAME","TRIGGER_NAME","TRIGGER_GROUP","CRON_EXPRESSION","TIME_ZONE_ID") VALUES ('UCenterScheduler','TASK_CLASS_NAME102','DEFAULT','0 0 0 ? * 1','Asia/Hong_Kong'); INSERT INTO "SYS_DICT_DATA" ("DICT_SORT","DICT_LABEL","DICT_VALUE","DICT_TYPE","CSS_CLASS","LIST_CLASS","IS_DEFAULT","STATUS","CREATE_BY","CREATE_TIME","UPDATE_BY","UPDATE_TIME","REMARK") VALUES (2,'服务中心','2','sys_link_type','','default','N','0','EoiAdmin','2023-06-09 14:22:42','EoiAdmin','2023-06-14 16:50:46',''), (1,'运管系统','1','sys_link_type',NULL,'default','N','0','EoiAdmin','2023-06-09 14:23:10','',NULL,NULL), (3,'轮播类型','3','sys_link_type',NULL,'default','N','0','EoiAdmin','2023-06-09 14:23:38','',NULL,NULL), (4,'报表类型','4','sys_link_type',NULL,'default','N','0','EoiAdmin','2023-06-26 15:00:13','EoiAdmin','2023-06-26 15:00:22',NULL); INSERT INTO "SYS_USER" ("DEPT_ID","USER_NAME","NICK_NAME","USER_TYPE","EMAIL","PHONENUMBER","SEX","AVATAR","PASSWORD","STATUS","DEL_FLAG","LOGIN_IP","LOGIN_DATE","CREATE_BY","CREATE_TIME","UPDATE_BY","UPDATE_TIME","REMARK","SSO_ID","SOURCE_BY","OTP_SK","SECRET_BASE32","EMPLOYEEID","EN_NAME","DEPARTMENT","LAST_PASSWORD_CHANGE_DATE") VALUES (103,'EoiAdmin','开发','00','','15888888888','0','','$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2','0','0','192.168.21.225','2024-07-09 11:56:23','admin','2023-02-14 11:03:47','','2024-07-08 23:56:23','管理员',20200321,'0','otpauth://totp/EoiAdmin@?secret=ISG5WRNRFRDXJAOQAEIJ33YDOBV2EUDOON766YF2TSODJ27XNIRQ&period=30&digits=6','ISG5WRNRFRDXJAOQAEIJ33YDOBV2EUDOON766YF2TSODJ27XNIRQ',NULL,NULL,NULL,'2024-07-08 23:56:23'), (103,'admin','管理员','00','[email protected]','15888880000','0','','$2a$10$erscMiICVJEZ7NjUA7kbZOXA2URJn3iSt2WdmvusqDVd7HzASIZWq','0','0','192.168.21.225','2024-07-10 11:40:09','admin','2023-02-14 11:03:47','admin','2024-07-09 23:40:08','管理员',20200321,'0','','6XJXUMWJACPXKLO3SU44HKDGEUDTQIG7PGJYHMLJO3W42GPIDSMA',NULL,NULL,NULL,'2024-07-09 23:40:08');

In the end, the large model successfully processed all SQL statements as required and achieved the expected results.

Lessons Learned

Through this practice, we summarized the following key experiences:

1. Clarify the needs:The prompt words must be clear and unambiguous, covering specific requirements and expected results, and avoiding ambiguity.

2. Provide examples:By providing multiple correct examples, you can help the large model learn and understand the requirements and output the correct results.

3. Feedback and Adjustment:Provide feedback on preliminary results, adjust prompt words and examples, and gradually optimize output effects.

4. Prompt word engineering:When designing prompt words, pay attention to logic and hierarchy so that the large model can gradually understand the task and execute it correctly.

Conclusion

In practical applications, using large models to handle complex tasks not only improves efficiency but also ensures the accuracy of the results. However, to fully tap the potential of large models, prompt engineering and example design are essential. In the future, I will continue to explore more application scenarios of large models in operation and maintenance, project management, and daily work to help everyone improve their work and learning efficiency.

Through the successful practice of SQL migration, I have become more confident in using large models to improve work efficiency and quality. I hope this experience summary can provide reference for more projects and teams.