Classificação Natural MySQL

Resumo : neste tutorial, você aprenderá como classificar dados usando a técnica de classificação natural no MySQL.

O que é classificação natural?

A classificação natural, também conhecida como “classificação humana” ou “classificação alfanumérica”, é uma forma de organizar dados que parece mais intuitiva para os humanos, especialmente quando se trata de dados que incluem uma mistura de números e texto.

A classificação natural ordena os itens com base em seu valor, como um ser humano esperaria, em vez de tratá-los puramente como strings.

Normalmente, a classificação natural segue estes princípios:

• Classifique os valores numéricos primeiro.
• Em seguida, classifique os valores do texto em segundo lugar.
• Ignore maiúsculas e minúsculas e zeros à esquerda.
• Finalmente, classifique símbolos e caracteres especiais usando seus valores ASCII ou Unicode.

Configurando uma tabela de exemplo

Primeiro, crie uma nova tabela chamada itemspara armazenar dados de amostra:

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A itemstabela possui duas colunas:

• id: esta coluna identifica exclusivamente cada linha da itemstabela. Possui o AUTO_INCREMENTatributo que gera automaticamente um número sequencial quando você insere uma nova linha na tabela.
• name: Esta coluna armazena o nome de cada item.

Segundo, insira algumas linhas na itemstabela:

INSERT INTO items(name)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, consulte os dados da itemstabela classificados pelos valores da namecoluna:

SELECT 
  name 
FROM 
  items 
ORDER BY 
  name;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------+
| name |
+------+
| 1    |
| 10Z  |
| 1C   |
| 2    |
| 20D  |
| 2A   |
| 3C   |
+------+
7 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

O conjunto de resultados pode não ser o que esperávamos. Esperamos ter o resultado classificado definido assim:

+------+
| name |
+------+
| 1    |
| 1C   |
| 2    |
| 2A   |
| 3C   |
| 10Z  |
| 20D  |
+------+
7 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Infelizmente, o MySQL não fornece nenhuma sintaxe ou função de classificação natural integrada.

Para realizar uma classificação natural, você pode usar várias técnicas. O exemplo a seguir mostra como usar expressões regulares para realizar a classificação natural.

Classificação natural do MySQL usando expressões regulares

Primeiro, divida a coluna de nome em duas partes usando a função REGEXP_SUBSTR() :

SELECT
  name, 
  CAST(REGEXP_SUBSTR(name, '^\\d+') AS SIGNED) prefix, 
  REGEXP_SUBSTR(name, '\\D$') suffix 
FROM
  items;Linguagem de código:  PHP  ( php )

Saída:

+------+--------+--------+
| name | prefix | suffix |
+------+--------+--------+
| 1    |      1 | NULL   |
| 1C   |      1 | C      |
| 10Z  |     10 | Z      |
| 2A   |      2 | A      |
| 2    |      2 | NULL   |
| 3C   |      3 | C      |
| 20D  |     20 | D      |
+------+--------+--------+
7 rows in set (0.00 sec)Linguagem de código:  PHP  ( php )

Neste exemplo, usamos expressões regulares para dividir os valores da namecoluna em partes numéricas e alfabéticas:

• O padrão '^\\d+'corresponde a um ou mais dígitos no início de uma string.
• O padrão '\\D$'corresponde a um ou mais caracteres não numéricos no final de uma string.

Como a REGEXP_SUBSTR()função retorna uma string, usamos CASTpara convertê-la em um número inteiro para a coluna de prefixo.

Como resultado, a prefixcoluna armazena a parte numérica e suffixa coluna armazena a parte alfabética dos valores na namecoluna.

Segundo, classifique os valores na namecoluna pelas colunas prefixe suffix:

SELECT 
  name, 
  CAST(
    REGEXP_SUBSTR(name, '^\\d+') AS SIGNED
  ) prefix, 
  REGEXP_SUBSTR(name, '\\D$') suffix 
FROM 
  items 
ORDER BY 
  prefix, 
  suffix;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------+--------+--------+
| name | prefix | suffix |
+------+--------+--------+
| 1    |      1 | NULL   |
| 1C   |      1 | C      |
| 2    |      2 | NULL   |
| 2A   |      2 | A      |
| 3C   |      3 | C      |
| 10Z  |     10 | Z      |
| 20D  |     20 | D      |
+------+--------+--------+
7 rows in set (0.01 sec)Linguagem de código:  PHP  ( php )

A consulta primeiro classifica os dados numericamente e depois em ordem alfabética. Obtemos o resultado esperado.

Se não quiser ter as colunas prefixe suffixna saída, você pode removê-las da seguinte maneira:

SELECT 
  name 
FROM 
  items 
ORDER BY 
  CAST(
    REGEXP_SUBSTR(name, '^\\d+') AS SIGNED
  ), 
  REGEXP_SUBSTR(name, '\\D$')Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Saída:

+------+
| name |
+------+
| 1    |
| 1C   |
| 2    |
| 2A   |
| 3C   |
| 10Z  |
| 20D  |
+------+
7 rows in set (0.00 sec)Linguagem de código:  JavaScript  ( javascript )

Observe que algumas linguagens de programação suportam funções de classificação natural, por exemplo, o PHP fornece a função natsort() que classifica um array usando um algoritmo de classificação natural .

Resumo

• Divida os dados em partes e classifique-as para obter uma classificação natural no MySQL.