Um guia para strings no MySQL
Aprenda como o MySQL armazena e exibe suas variáveis de string para que você possa ter melhor controle sobre seus dados.
Strings são um dos tipos de dados mais comuns que você usará no MySQL. Muitos usuários inserem e leem strings em seus bancos de dados sem pensar muito nelas. Este artigo tem como objetivo fornecer um mergulho profundo em como o MySQL armazena e exibe suas variáveis de string para que você possa ter melhor controle sobre seus dados.
Você pode dividir strings em duas categorias: binárias e não binárias. Você provavelmente pensa em strings não binárias na maior parte do tempo. Strings não binárias possuem conjuntos de caracteres e agrupamentos. As strings binárias, por outro lado, armazenam coisas como arquivos MP3 ou imagens. Mesmo que você armazene uma palavra em uma string binária, como música, ela não será armazenada da mesma maneira que em uma string não-binária.
Vou me concentrar em strings não binárias. Todas as strings não binárias no MySQL estão associadas a um conjunto de caracteres e a um agrupamento. O conjunto de caracteres de uma string controla quais caracteres podem ser armazenados na string, e seu agrupamento controla como as strings são ordenadas quando você as exibe.
Conjuntos de caracteres
Para visualizar os conjuntos de caracteres em seu sistema, execute o seguinte comando:
SHOW CHARACTER SET;Este comando produzirá quatro colunas de dados, incluindo o conjunto de caracteres:
- Nome
- Descrição breve
- Agrupamento padrão
- Tamanho máximo de cada caractere no conjunto de caracteres
O MySQL costumava usar como padrão o conjunto de caracteres latin1, mas desde a versão 8.0, o padrão tem sido utf8mb4. O agrupamento padrão agora é utf8mb4_0900_ai_ci. O ai indica que este agrupamento não faz distinção entre acentos (á=a) e o ci especifica que não faz distinção entre maiúsculas e minúsculas (a=UMA).
Diferentes conjuntos de caracteres armazenam seus caracteres em blocos de memória de vários tamanhos. Por exemplo, como você pode ver no comando acima, os caracteres armazenados em utf8mb4 são armazenados na memória com tamanho de um a quatro bytes. Se você quiser ver se uma string possui caracteres multibyte, você pode usar as funções CHAR_LENGTH() e LENGTH(). CHAR_LENGTH() exibe quantos caracteres uma string contém, enquanto LENGTH() mostra quantos bytes uma string possui, que pode ou não ser igual ao comprimento de uma string em caracteres, dependendo do conjunto de caracteres. Aqui está um exemplo:
SET @a = CONVERT('data' USING latin1);
SELECT LENGTH(@a), CHAR_LENGTH(@a);
+------------+-----------------+
| LENGTH(@a) | CHAR_LENGTH(@a) |
+------------+-----------------+
| 4 | 4 |
+------------+-----------------+Este exemplo mostra que o conjunto de caracteres latin1 armazena caracteres em unidades de byte único. Outros conjuntos de caracteres, como utf16, permitem caracteres multibyte:
SET @b = CONVERT('data' USING utf16);
SELECT LENGTH(@b), CHAR_LENGTH(@b);
+------------+------------------+
| LENGTH(@b) | CHAR_LENGTH(@b) |
+------------+------------------+
| 8 | 4 |
+------------+------------------+Agrupamento
O agrupamento de uma string determinará como os valores serão exibidos quando você executar uma instrução SQL com uma cláusula ORDER BY. Sua escolha de agrupamentos é determinada pelo conjunto de caracteres selecionado. Ao executar o comando SHOW CHARACTER SET acima, você viu os agrupamentos padrão para cada conjunto de caracteres. Você pode ver facilmente todos os agrupamentos disponíveis para um determinado conjunto de caracteres. Por exemplo, se você quiser ver quais agrupamentos são permitidos pelo conjunto de caracteres utf8mb4, execute:
SHOW COLLATION LIKE 'utf8mb4%';Um agrupamento pode diferenciar maiúsculas de minúsculas, diferenciar maiúsculas de minúsculas ou binário. Vamos construir uma tabela simples, inserir alguns valores nela e, em seguida, visualizar os dados usando diferentes agrupamentos para ver como a saída difere:
CREATE TABLE sample (s char(5));
INSERT INTO sample (s) VALUES
('AAAAA'), ('ccccc'), ('bbbbb'), ('BBBBB'), ('aaaaa'), ('CCCCC');
SELECT * from sample;
+-----------+
| s |
+-----------+
| AAAAA |
| ccccc |
| bbbbb |
| BBBBB |
| aaaaa |
| CCCCC |
+-----------+Com agrupamentos que não diferenciam maiúsculas de minúsculas, seus dados são retornados em ordem alfabética, mas não há garantia de que as palavras em maiúsculas virão antes das palavras em minúsculas, conforme mostrado abaixo:
SELECT * from sample ORDER BY s COLLATE utf8mb4_turkish_ci;
+-----------+
| s |
+-----------+
| AAAAA |
| aaaaa |
| bbbbb |
| BBBBB |
| ccccc |
| CCCCC |
+-----------+Por outro lado, quando o MySQL executa uma pesquisa com distinção entre maiúsculas e minúsculas, as letras minúsculas virão antes das maiúsculas para cada letra:
SELECT * from sample ORDER BY s COLLATE utf8mb4_0900_as_cs;
+-----------+
| s |
+-----------+
| aaaaa |
| AAAAA |
| bbbbb |
| BBBBB |
| ccccc |
| CCCCC |
+-----------+E os agrupamentos binários retornarão todas as palavras em maiúsculas antes das palavras em minúsculas:
SELECT * from sample ORDER BY s COLLATE utf8mb4_0900_bin;
+-----------+
| s |
+-----------+
| AAAAA |
| BBBBB |
| CCCCC |
| aaaaa |
| bbbbb |
| ccccc |
+-----------+Se você quiser saber qual conjunto de caracteres e agrupamento uma string usa, você pode usar as funções apropriadamente chamadas charset e agrupamento. Um servidor executando o MySQL versão 8.0 ou superior usará como padrão o conjunto de caracteres utf8mb4 e o agrupamento utf8mb4_0900_ai-ci:
SELECT charset('data');
+-------------------+
| charset('data') |
+-------------------+
| utf8mb4 |
+-------------------+
SELECT collation('data');
+--------------------+
| collation('data') |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+Você pode usar o comando SET NAMES para alterar o conjunto de caracteres ou agrupamento usado.
Para mudar do conjunto de caracteres utf8mb4 para utf16, execute este comando:
SET NAMES 'utf16';Se você também quiser escolher um agrupamento diferente do padrão, você pode adicionar uma cláusula COLLATE ao comando SET NAMES.
Por exemplo, digamos que seu banco de dados armazene palavras no idioma espanhol. O agrupamento padrão do MySQL (utf8mb4_0900_ai_ci) vê ch e ll como dois caracteres diferentes e os classifica como tal. Mas em espanhol, ch e ll são letras individuais, portanto, se você quiser classificá-las na ordem correta (seguindo c e l, respectivamente), será necessário usar um agrupamento diferente. Uma opção é usar o agrupamento utf8mb4_spanish2_ci.
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_spanish2-ci';Armazenando strings
MySQL permite que você escolha entre vários tipos de dados para seus valores de string. (Ainda mais do que outros bancos de dados populares, como PostgreSQL e MongoDB.)
Aqui está uma lista dos tipos de dados de string binária do MySQL, seus equivalentes não binários e seu comprimento máximo:
- binário: caractere (255)
- varbinário: varchar (65.535)
- tinyblob: tinytext (255)
- blob: texto (65.535)
- blob médio: texto médio (16.777.215)
- blob longo: texto longo (4.294.967.295)
Uma coisa importante a lembrar é que, diferentemente dos tipos varbinary, varchar, text e blob, que são armazenados em campos de comprimento variável (ou seja, usando apenas o espaço necessário), o MySQL armazena tipos binários e char em campos de comprimento fixo. Portanto, um valor como char(20) ou binary(20) sempre ocupará 20 bytes, mesmo se você armazenar menos de 20 caracteres neles. O MySQL preenche os valores com o valor ASCII NUL (0x00) para tipos binários e espaços para tipos char.
Outra coisa a considerar ao escolher os tipos de dados é se você deseja que os espaços após a string sejam preservados ou eliminados. Ao exibir dados, o MySQL remove espaços em branco dos dados armazenados com o tipo de dados char, mas não varchar.
CREATE TABLE sample2 (s1 char(10), s2 varchar(10));
INSERT INTO sample2 (s1, s2) VALUES ('cat ', 'cat ');
SELECT s1, s2, CHAR_LENGTH(s1), CHAR_LENGTH(s2) from sample2;
+---------+---------+-----------------------------------+
| s1 | s2 | CHAR_LENGTH(s1) | CHAR_LENGTH(s2) |
+---------+---------+-----------------------------------+
| cat | cat | 3 | 10 |
+---------+---------+-----------------------------------+Embrulhar
Strings são um dos tipos de dados mais comuns usados em bancos de dados, e o MySQL continua sendo um dos sistemas de banco de dados mais populares em uso atualmente. Espero que você tenha aprendido algo novo com este artigo e seja capaz de usar seu novo conhecimento para melhorar suas habilidades em banco de dados.