SQL Infinitus Classification Query and superior ID query commands

First, let's take a look at mysql's CONCAT (str1, str2,…) function ,...)

Returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL. There may be one or more parameters. If all parameters are non-binary strings, the result is a non-binary string. If the independent variable contains any binary string, the result is a binary string. A numeric parameter is converted to an equivalent binary string format. To avoid this, you can use an explicit cast, for example: select concat (CAST (int_col as char ), char_col)

Mysql> select concat ('My, s', 'ql ');

-> 'Mysql'

Mysql> select concat ('My, NULL, 'ql ');

-> NULL

Mysql> select concat (14.3 );

-> '14. 3'

Note: CONCAT_WS

CONCAT_WS (separator, str1, str2 ,...)
CONCAT_WS () represents CONCAT With Separator, which is a special form of CONCAT. The first parameter is the delimiter of other parameters. The separator is placed between the two strings to be connected. The delimiter can be a string or another parameter. If the delimiter is NULL, the result is NULL. The function ignores the NULL value after any separator parameter.

Mysql> SELECT CONCAT_WS (',', 'first name', 'second name', 'last name ');

-> 'First name, Second name, Last name'

Mysql> SELECT CONCAT_WS (',', 'first name', NULL, 'last name ');

-> 'First name, Last name'

CONCAT_WS () does not ignore any null strings. (However, all NULL values are ignored ).

 

Example:

First, let's take a look at the database table:

Database SQL:

Create table 'sh _ privilege '(
'Id' tinyint (3) unsigned not null AUTO_INCREMENT COMMENT 'id ',
'Pri _ name' varchar (15) not null comment 'permission name ',
'Module _ name' varchar (15) not null comment' corresponding module name ',
'Controller _ name' varchar (15) not null comment' corresponding controller name ',
'Action _ name' varchar (15) not null comment' corresponding method name ',
'Parent _ id' tinyint (3) unsigned not null default '0' comment' superior ID ',
'Pri _ level' tinyint (3) unsigned not null default '0' comment' is level 0: Top level 1: level 2: level 3, purpose: shrink near ',
'Pri _ path' varchar (15) not null default '0' comment' parent path ID, for example, 0-2-6. Purpose: tree structure can be implemented without recursion ',
Primary key ('id '),
KEY 'pri _ path' ('pri _ path ')
) ENGINE = MyISAM AUTO_INCREMENT = 18 default charset = utf8 COMMENT = 'permission table ';
SQL method for wireless classification: SELECT id, pri_path FROM sh_privilege ORDER BY CONCAT (pri_path, '-', id );

Take the sub-permission of 8: SELECT id, pri_path FROM sh_privilege where concat ('-', pri_path, '-') LIKE '%-8-% ';