The cascade removal problem encountered when MYSQL uses recursive functions

The following two paragraphs of SQL appear to be the same, but the results are different.

notation a:

DELETE om_organization,om_position  from om_organization  Left JOIN  on = om_organization.org_id WHERE Find_in_set (om_organization.org_id, om_organization_query_children (' c14>52037b7c-0f01-41f6-849f-4f99ad8f2422'

This is not the correct wording, first to introduce this

Om_organization_query_children

Function

BEGINDECLAREStempVARCHAR(4000);DECLAREStempchdVARCHAR(4000);SETStemp= '$';SETStempchd= ID; whileStempchd is  not NULL DoSETStemp=CONCAT (Stemp,',', stempchd);SELECTGroup_concat (org_id) intoStempchd from om_organization whereFind_in_set (parent_org_id, Stempchd)>0;END  while;returnstemp;END

ID (VARCHAR (40)) is the parameter of this function

The result of this function is to return a combined string, the string according to the primary key org_id, parent node primary key parent_org_id two fields, recursive query out om_organization All of the primary keys in the root node are passed in as arguments, and then a string similar to "1,2,3,4" is used for Find_in_set (defined here as varchar (4000)).

A: Each deletion of data can cause Om_organization_query_children (' 1 ') to change the execution result of the function,

Assuming that the first rule is deleted, the result of this function is "three-way", where ' 2 ' parent node is ' 1 ', ' 3 ' parent node is ' 1 ', then after the deletion of ' 2 ', the result of the function becomes "1,3", and the second side has a relationship with ' 2 ' om_ Position can not be deleted.

The correct wording should be as follows:

Notation B:

DELETEom_organization, Om_position from    (        SELECTorg_id fromom_organizationWHEREFind_in_set (om_organization.org_id, Om_organization_query_children ('52037b7c-0f01-41f6-849f-4f99ad8f2422')) ) TINNER JOINOm_organization ont.org_id=om_organization.org_id Left JOINOm_position onom_position.org_id=om_organization.org_id

This way, the function executes only once, and the result will remain unchanged until the end of SQL. Not only improves efficiency, but also avoids mistakes.

The cascade removal problem encountered when MYSQL uses recursive functions