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 items
para 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 items
tabela possui duas colunas:
id
: esta coluna identifica exclusivamente cada linha daitems
tabela. Possui oAUTO_INCREMENT
atributo 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 items
tabela:
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 items
tabela classificados pelos valores da name
coluna:
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 name
coluna 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 CAST
para convertê-la em um número inteiro para a coluna de prefixo.
Como resultado, a prefix
coluna armazena a parte numérica e suffix
a coluna armazena a parte alfabética dos valores na name
coluna.
Segundo, classifique os valores na name
coluna pelas colunas prefix
e 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 prefix
e suffix
na 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.