Ý nghĩa của bức tranh trên: Chiến thắng trong mọi trận chiến, cố gắng và chân thật.

câu chuyện

Lập trình viên Xiao Zhang: Tôi mới tốt nghiệp và đi làm được khoảng một năm. Công việc hàng ngày của tôi là CRUD.

Kiến trúc sư Lao Li: Có kinh nghiệm thực hiện nhiều dự án quy mô lớn, thành thạo các kỹ năng kiến ​​trúc phát triển khác nhau;.

Xiao Zhang nhận thấy rằng trong các kịch bản phát triển dự án thực tế, nhiều nhà phát triển chỉ tập trung vào việc viết các tập lệnh SQL để đáp ứng các yêu cầu về chức năng, đồng thời bỏ qua tính lặp lại của các tập lệnh.

Điều này có nghĩa là nếu một phần nhất định của tập lệnh không thực thi được, nhân viên vận hành và bảo trì phải cung cấp tập lệnh mới từ đầu, đây là một thách thức đối với nhóm vận hành và bảo trì cũng như các nhà phát triển.

Vì vậy, Xiao Zhang quyết định nghiên cứu cách viết các tập lệnh SQL dựa trên MySQL có thể được thực thi nhiều lần để nâng cao hiệu quả phát triển và đơn giản hóa quy trình vận hành và bảo trì.

Ông đã tham khảo ý kiến ​​kiến ​​trúc sư Lao Li của công ty về vấn đề này. Lao Li là một kiến ​​trúc sư giàu kinh nghiệm.

Ông đã tích lũy được nhiều kinh nghiệm quý báu trong nhiều dự án quy mô lớn và thành thạo các kiến ​​trúc phát triển khác nhau.

Sau khi nghe câu hỏi của Xiao Zhang, Lao Li mỉm cười và bắt đầu hướng dẫn. Anh ấy giải thích cho Xiao Zhang cách viết một tập lệnh SQL có thể tái tạo và chia sẻ những điểm chính sau:

a. Sử dụng giao dịch: Giao dịch là một đơn vị logic của một nhóm câu lệnh SQL, có thể đảm bảo rằng tất cả các câu lệnh trong nhóm câu lệnh này đều được thực thi thành công hoặc được khôi phục.

                        
                             Bằng cách sử dụng các giao dịch, bạn có thể đảm bảo rằng tất cả các sửa đổi đối với tập lệnh đều được thực thi đầy đủ hoặc không được thực thi.

                        
                      

b. Sử dụng kiểm tra có điều kiện: Trước mỗi câu lệnh cần sửa đổi dữ liệu, hãy thêm kiểm tra có điều kiện để đảm bảo rằng việc sửa đổi chỉ được thực hiện khi dữ liệu không tồn tại hoặc đáp ứng các điều kiện cụ thể.

                        
                             Điều này tránh việc chèn cùng một dữ liệu nhiều lần hoặc thực hiện các thao tác cập nhật không cần thiết.

                        
                      

c. Xử lý lỗi: Khi viết tập lệnh, hãy xem xét các tình huống lỗi có thể xảy ra và đưa ra cơ chế xử lý lỗi thích hợp. Ví dụ: sử dụng câu lệnh IF...ELSE để xử lý logic thực thi trong các điều kiện cụ thể.

d. Sử dụng các thủ tục được lưu trữ: Nếu các tập lệnh rất phức tạp và chứa nhiều bước và logic nghiệp vụ, hãy xem xét việc đóng gói chúng vào các thủ tục được lưu trữ. Điều này cho phép tổ chức và quản lý mã tốt hơn, đồng thời làm cho các tập lệnh dễ đọc và dễ bảo trì hơn.

Xiao Zhang vô cùng thích thú lắng nghe và bắt đầu áp dụng những lời đề nghị của Lão Li vào thực tế. Anh đã nghiên cứu kỹ từng câu lệnh SQL và sửa đổi, tối ưu hóa nó theo hướng dẫn của Lao Li.

Anh ấy đã sử dụng các giao dịch để bao bọc toàn bộ tập lệnh, thêm các kiểm tra có điều kiện để tránh việc chèn dữ liệu lặp lại và triển khai các cơ chế xử lý lỗi để xử lý các tình huống bất thường.

lý lịch

Do đó, có một số yêu cầu cơ bản nhất định để phát triển các tập lệnh SQL để vận hành và bảo trì:

1. Có thể được thực hiện nhiều lần;

2. Không có lỗi, (không có lỗi, logic đúng); .

Nếu tập lệnh không thể được thực thi nhiều lần thì việc vận hành và bảo trì không thể được tự động hóa, do đó yêu cầu các nhà phát triển phụ trợ phải cung cấp các tập lệnh SQL mới thích ứng với môi trường hiện tại, làm tăng chi phí vận hành, bảo trì và truyền thông.

Vậy làm thế nào để viết một tập lệnh SQL có thể tái tạo được?

Chia nó thành 4 kịch bản để giới thiệu ví dụ.

1 Tạo bảng

                        
                          tạo bảng nếu không tồn tại nginx_config ( id varchar(36) not null default '' comment 'UUID', namespace varchar(255) not null default '' comment 'environment namespace', config_content text comment "nginx http block configure", content_md5 varchar (64) không null mặc định '' nhận xét 'Giá trị MD5 của nội dung cấu hình', trình điều khiển varchar(64) không null mặc định '' nhận xét 'Toán tử', description varchar(512) not null default '' comment 'Description', gmt_created bigint unsigned not null mặc định 0 comment 'Thời gian tạo', khóa chính(id) )ENGINE=InnoDB comment 'bảng cấu hình nginx';

                        
                      

Xóa bảng bị cấm trong môi trường sản xuất.

Sửa đổi tên bảng ở chế độ sao lưu

Sửa đổi tên bảng: Trước tiên, hãy tạo một bảng mới, sau đó sao chép dữ liệu lịch sử vào đó.

                        
                          DELIMITER // xóa thủ tục nếu tồn tại modify_table_name; CREATE PROCEDURE modify_table_name(IN table_name VARCHAR(255), IN new_name VARCHAR(255) ) BEGIN DECLARE database_name VARCHAR(255); DECLARE table_exists INT DEFAULT 0; DECLARE new_table_exists INT DEFAULT 0; SELECT DATABASE() INTO database_name; set @db_table_name=concat(database_name,'/',table_name); select count(t1.TABLE_ID) INTO table_exists from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name ; set @db_table_name_new=concat(database_name,'/',new_name); select count(t1.TABLE_ID) INTO new_table_exists from information_schema.INNODB_TABLES t1 where t1.NAME=@db_table_name_new ; IF table_exists = 1 AND new_table_exists = 0 THEN SET @query = CONCAT('create table ',new_name,' like ',table_name); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @query = CONCAT('insert into ', new_name, ' select * from ',table_name); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT 'sửa đổi tên bảng thành công.' AS result ,@db_table_name,@db_table_name_new,table_exists,new_table_exists; ELSE SELECT 'tên bảng không tồn tại hoặc new_name đã tồn tại.' AS kết quả, @db_table_name, @db_table_name_new, table_exists, new_table_exists; KẾT THÚC NẾU; KẾT THÚC // DẤU PHÉP GIỚI HẠN;

                        
                      

Kịch bản thử nghiệm:

                        
                          tạo bảng người dùng (id bigint auto_increment khóa chính, tên varchar (30), tuổi tinyint) bình luận 'user表'; chèn vào người dùng (id, tên, tuổi) GIÁ TRỊ (1, 'a', 1), (2, 'b', 2), (3, 'c', 3); gọi modify_table_name ('user', 'user1'); chọn * từ người dùng 1; gọi modify_table_name ('user', 'user2'); chọn * từ người dùng 2;

                        
                      

Kết quả kiểm tra đúng như mong đợi.

Thêm, sửa, xóa các trường

                        
                          thủ tục xóa nếu tồn tại trường_bảng_sửa đổi; TẠO THỦ TỤC sửa đổi trường_bảng(TRONG_tên_bảng VARCHAR(50), TRONG_tên_trường VARCHAR(50), TRONG_hành_động_trường VARCHAR(10), TRONG_loại_trường VARCHAR(255)) BẮT ĐẦU NẾU_hành_động_trường = 'thêm' THÌ NẾU KHÔNG TỒN TẠI (CHỌN * TỪ_thông_tin_lược_cột_nơi_nào_tên_bảng = tên_bảng VÀ_tên_cột = tên_trường) THÌ ĐẶT @query = CONCAT('ALTER TABLE ', tableName, ' THÊM_CỘT ', fieldName, ' ', fieldType); CHUẨN BỊ stmt TỪ @query; THỰC THI stmt; GIẢI PHÓNG STMT CHUẨN BỊ stmt; CHỌN 'Trường đã được thêm thành công.' NHƯ kết quả; NẾU KHÔNG CHỌN 'Trường đã tồn tại.' NHƯ kết quả; KẾT THÚC NẾU; ELSEIF fieldAction = 'sửa đổi' THÌ NẾU TỒN TẠI (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN SET @query = CONCAT('ALTER TABLE ', tableName, ' CHANGE COLUMN ', fieldName, ' ', fieldName, ' ', fieldType); select @query; PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT 'Trường đã sửa đổi thành công.' AS result; ELSE SELECT 'Trường không tồn tại hoặc có tên giống nhau.' AS result; END IF; ELSEIF fieldAction = 'delete' THÌ NẾU TỒN TẠI (SELECT * FROM information_schema.columns WHERE table_name = tableName AND column_name = fieldName) THEN SET @query = CONCAT('ALTER TABLE ', tableName, ' DROP COLUMN ', fieldName); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT 'Trường đã xóa thành công.' AS result; ELSE SELECT 'Trường không tồn tại.' AS result; END IF; ELSE SELECT 'Hành động trường không hợp lệ.' AS result; END IF; END;


                        
                      

kịch bản thử nghiệm

                        
                          tạo bảng nếu không tồn tại sys_agent ( Agent_id bigint không null nhận xét 'id duy nhất của dịch vụ khách hàng' khóa chính, Agent_name varchar(64) null nhận xét 'tên dịch vụ khách hàng', Agent_type varchar(30) null nhận xét 'loại dịch vụ khách hàng (dịch vụ khách hàng địa điểm, dịch vụ khách hàng trực tiếp) ', quận varchar(30) null bình luận 'quận', service_linguvarchar(30) null bình luận 'ngôn ngữ dịch vụ', Agent_description varchar(500) null comment 'Mô tả khách hàng', trạng thái tinyint(1) null comment 'Trạng thái (0=invalid, 1=valid), mặc định là 1', del_flag tinyint(1) null comment 'Có nên xóa (0=false) , 1=true)', user_id bigint null nhận xét 'id người dùng (thông tin người dùng được liên kết)', time_zone varchar(50) null nhận xét 'múi giờ', create_by varchar(50) null nhận xét 'người sáng tạo', create_time datetime mặc định CURRENT_TIMESTAMP null nhận xét 'Thời gian tạo', update_by varchar(50) null nhận xét 'Modifier', update_time datetime mặc định CURRENT_TIMESTAMP null khi cập nhật nhận xét CURRENT_TIMESTAMP 'Thời gian sửa đổi' ) nhận xét 'Quản lý dịch vụ khách hàng'; 'sex', 'add', 'tinyint không null nhận xét ''giới tính'''); GỌI Modify_table_field('sys_agent', 'sex2', 'add', 'tinyint not null comment ''sex'''); GỌI Modify_table_field('sys_agent', 'sex', 'modify', 'int not null comment '' Giới tính'''); GỌI mod_table_field('sys_agent', 'sex', 'xóa', ''); sửa đổi_table_field('sys_agent', 'sex2', 'xóa', '');


                        
                      

Kết quả kiểm tra đúng như mong đợi.

Thêm, sửa, xóa chỉ mục

Thường được đặt trong câu lệnh tạo bảng, trong 80% trường hợp;

Nếu chỉ mục được thêm vào sau trong dự án để điều chỉnh, bạn có thể tham khảo các trường và viết một thủ tục lưu sẵn để hỗ trợ việc thực hiện lặp lại chỉ mục mới;

                        
                          DELIMITER // xóa thủ tục nếu tồn tại modify_table_index; CREATE PROCEDURE modify_table_index( IN table_name VARCHAR(255), IN index_name VARCHAR(255), IN index_action ENUM('add', 'modify', 'delete'), IN index_columns VARCHAR(255) ) BEGIN DECLARE database_name VARCHAR(255); DECLARE index_exists INT DEFAULT 0; DECLARE index_exists_action INT DEFAULT 0; -- Không thể xóa cơ sở dữ liệu SELECT DATABASE() INTO database_name; set @db_table_name=concat(database_name,'/',table_name); -- Không thể thay đổi giá trị của bảng select count(t2.INDEX_ID) INTO index_exists from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name; set index_exists_action=index_exists; IF index_action = 'add' THEN -- Không thể thay đổi giá trị IF index_exists < 1 THEN SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name; SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action; ELSE SELECT 'Index already exists.' AS result ,database_name,index_exists,@db_table_name,index_exists_action; END IF; ELSEIF index_action = 'modify' THEN -- Thay đổi giá trị (Không thay đổi giá trị) IF index_exists > 0 THEN SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; chọn count(t2.INDEX_ID) VÀO index_exists_action từ information_schema.INNODB_TABLES t1 còn lại tham gia information_schema.INNODB_INDEXES t2 trên t1.TABLE_ID=T2.TABLE_ID trong đó t1.NAME=@db_table_name và t2.NAME=index_name; SELECT 'Chỉ mục đã được sửa đổi thành công.' AS result,database_name,index_exists,@db_table_name,index_exists_action; ELSE SELECT 'Chỉ mục không tồn tại. create' AS result,database_name,index_exists,@db_table_name,index_exists_action; SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name; SELECT 'Chỉ mục đã được thêm thành công.' AS result ,database_name,index_exists,@db_table_name,index_exists_action; END IF; ELSEIF index_action = 'delete' THEN -- Xóa dữ liệu IF index_exists > 0 THEN SET @query = CONCAT('ALTER TABLE `', database_name, ''.`', table_name, '' DROP INDEX `', index_name, ''`'); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name; CHỌN 'Đã xóa chỉ mục thành công.' AS result,database_name,index_exists,@db_table_name,index_exists_action; ELSE SELECT 'Chỉ mục không tồn tại.' AS result,database_name,index_exists,@db_table_name,index_exists_action; END IF; ELSE SELECT 'Hành động chỉ mục không hợp lệ.' AS result,database_name,index_exists,@db_table_name,index_exists_action; END IF; END // DELIMITER ;



                        
                      

kịch bản thử nghiệm

                        
                          tạo bảng nếu không tồn tại sys_agent ( Agent_id bigint không null nhận xét 'id duy nhất của dịch vụ khách hàng' khóa chính, Agent_name varchar(64) null nhận xét 'tên dịch vụ khách hàng', Agent_type varchar(30) null nhận xét 'loại dịch vụ khách hàng (dịch vụ khách hàng địa điểm, dịch vụ khách hàng trực tiếp) ', quận varchar(30) null bình luận 'quận', service_linguvarchar(30) null bình luận 'ngôn ngữ dịch vụ', Agent_description varchar(500) null comment 'Mô tả khách hàng', trạng thái tinyint(1) null comment 'Trạng thái (0=invalid, 1=valid), mặc định là 1', del_flag tinyint(1) null comment 'Có nên xóa (0=false) , 1=true)', user_id bigint null nhận xét 'id người dùng (thông tin người dùng được liên kết)', time_zone varchar(50) null nhận xét 'múi giờ', create_by varchar(50) null nhận xét 'người sáng tạo', create_time datetime mặc định CURRENT_TIMESTAMP null nhận xét 'Thời gian tạo', update_by varchar(50) null nhận xét 'Công cụ sửa đổi', update_time datetime mặc định CURRENT_TIMESTAMP null khi cập nhật nhận xét CURRENT_TIMESTAMP 'Thời gian sửa đổi' ) nhận xét 'Quản lý dịch vụ khách hàng'; 'ix_agentName', 'thêm', 'agent_name,agent_type'); GỌI Modify_table_index('sys_agent', 'ix_agentName', 'delete', ''); GỌI Modify_table_index('sys_agent', 'ix_agentName', 'modify', 'agent_name,agent_type');

                        
                      

Thêm dữ liệu mới

Câu lệnh thay thế dựa trên khóa chính hoặc giá trị duy nhất. Nếu nó tồn tại, nó sẽ bị xóa trước và sau đó được chèn vào.

Lưu ý: Hãy nhớ ghi tên trường.

                        
                          THAY THẾ VÀO route_config (route_id, route_order, route_uri, route_filters, route_predicates, route_metadata, memo, created, updated, deleted) VALUES ('app-metadata-runtime', 1, 'lb://app-metadata-runtime', '[{"name":"StripPrefix","args":{"parts":"2"}}]', '[{"name":"Path","args":{"pattern":"/api/mr/**"}}]', '{}', '云枢服务app-metadata-runtime', '2020-07-31 21:44:11', '2020-09-07 20:24:13', 0);

                        
                      

bản tóm tắt

Các thủ tục lưu trữ tương ứng được viết theo các kịch bản khác nhau, do đó việc sửa đổi các trường, sửa đổi chỉ mục, sửa đổi bảng và chèn dữ liệu có thể được thực hiện nhiều lần.

Nếu bạn có bất kỳ câu hỏi sử dụng hoặc đề xuất tối ưu hóa nào, vui lòng gửi chúng. Khôi phục và liên lạc với tôi,

Là nguyên bản không dễ, nhưng sự chân thành mới có giá trị, giá đăng lại càng cao! Vui lòng ghi rõ nguồn khi in lại để chúng ta cùng trao đổi và cùng nhau tiến bộ.

Cuối cùng, bài viết này nói về các lập trình viên: Làm thế nào để bạn viết các câu lệnh SQL lặp lại? Bài viết này chỉ vậy thôi, nếu bạn muốn biết thêm về lập trình viên: Làm cách nào để viết các câu lệnh SQL có thể lặp lại? Về nội dung, vui lòng tìm kiếm các bài viết của CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .