Perguntas[collation](dba)

Descobri que as consultas abaixo retornam resultados diferentes em dois bancos de dados de hiperescala do Azure que estão executando o mesmo nível de compatibilidade, as mesmas opções de conjunto etc.

IF CAST(ServerProperty('Edition') AS nvarchar(128)) = 'SQL Azure' BEGIN
    SELECT 1
END
IF ServerProperty('Edition') = 'SQL Azure' BEGIN
    SELECT 2
END
IF 'SQL Azure' = ServerProperty('Edition') BEGIN
    SELECT 3
END

Em um banco de dados, ele retorna apenas 1, em outro banco de dados retorna 1,2 e 3.

Eu investiguei a causa raiz e parece ser causada por diferentes agrupamentos dos bancos de dados.

Para as seguintes consultas:

SELECT SQL_VARIANT_PROPERTY(ServerProperty('Edition'), 'Collation')
SELECT name, collation_name, compatibility_level FROM sys.databases

O banco de dados que retorna apenas uma linha, o resultado é:

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP850_CI_AS   150

E o resultado do banco de dados que retorna 1,2,3 é:

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP1_CI_AS     150

Portanto, a comparação simples sem o elenco está comparando sql_variantcom varchar(não há diferença quando eu uso N'SQL Azure') onde o subjacente nvarchardo sql_varianttem em um caso um agrupamento diferente do banco de dados que eu consulto e, em outro caso, é correspondente.

Em primeiro lugar, eu diria que a comparação de duas strings com agrupamento diferente falharia como falha quando você tenta unir duas colunas com agrupamento diferente, mas aparentemente não é o caso aqui.

De qualquer forma, qual é a melhor maneira de comparar com segurança a saída de uma função que pode estar sql_variantcom um varchar?

Uma LIKEcláusula pode testar se uma string ocorre em outra e a CHARINDEXfunção pode fornecer a posição inicial da primeira correspondência.

No meu caso, estou interessado na posição final , que é, devido aos meandros dos agrupamentos, não derivável do local inicial. Por exemplo, em um agrupamento alemão ( German_PhoneBook_100_CI_AS_SC_UTF8),

• häocorre em 'Häger' na posição 1 e termina na posição 2 e
• häocorre em 'Haeger' na posição 1 e termina na posição 3.

O problema disso é marcar a parte correspondente de um texto de resultado de pesquisa para o benefício dos usuários.

Eu estive pensando em inverter as cordas, mas ainda posso obter apenas a primeira correspondência CHARINDEXe, nesse caso invertido, precisaria da última.

Alguma ideia alguém?

Para fins de demonstração, tenho uma tabela de exemplo com uma varcharcoluna chamada numberstringcom os valores:

-4
32
0
4
16
8
-8
1024

Ao selecionar usando ORDER BY numberstring, acho que usuários diferentes obtêm resultados diferentes.

Alguns recebem:

-4, -8, 0, 1024, 16, 32, 4, 8

enquanto alguns recebem:

0, 1024, 16, 32, -4, 4, -8, 8

De alguma forma, o segundo resultado parece ignorar o sinal de menos, mesmo que os dados sejam classificados como dados de string.

Até onde eu sei, o servidor subjacente é uma instalação padrão do Microsoft SQL Server express, sem nenhuma opção especial. A versão está nos últimos anos, mas usuários com a mesma versão ainda relatam resultados diferentes.

Há algo na configuração que afeta os resultados e como posso visualizar isso no SSMS?

O servidor de banco de dados e o SSMS são configurados na Austrália apenas com os padrões, até onde eu saiba. O tipo de dados é varchar(max).

Estou tentando definir o agrupamento para um novo banco de dados no PostgreSQL 13, mas parece não surtir efeito:

postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';
CREATE DATABASE
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 assets    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Como posso fazer com que o novo banco de dados reflita minhas alterações de agrupamento?

Tudo bem, então eu tenho um banco de dados MariaDB e acabei de notar alguns problemas estranhos de armazenamento, como o seguinte:

• Minhas JSONcolunas (que no MariaDB são LONGTEXT) têm um agrupamento definido automaticamente de utf8mb4_bin. Acabei de notar que isso realmente atrapalha todos os meus apóstrofos, armazenando-os como ', também armazena écomo \u00e9, etc.

• Algumas outras colunas contêm letras francesas / espanholas / portuguesas etc. e são exibidas écomo de éfato. O agrupamento que ele usa é utf8mb4_unicode_ci.

• Outra coluna que contém strings que podem incluir marcação HTML usa utf8mb4_unicode_ci, e não há problemas com a marcação. Mas, por algum motivo, escapa das aspas simples, portanto, armazena 'como \', o que deve ser evitado.

Então, minha pergunta é, qual agrupamento você usaria idealmente para não ter problemas com o armazenamento de marcação HTML, caracteres especiais como é, ö, ä, è, e não apóstrofos de escape, no MariaDB?

ATUALIZAR

Na verdade, eu realmente não entendo porque isso acontece:

• Na minha tabela de banco de dados de exemplo, tenho duas colunas, A e B. O conjunto de caracteres da tabela é utf8mb4, e nenhuma coluna tem um conjunto de caracteres atribuído especificamente.

• A tabela usa o agrupamento padrãoutf8mb4_unicode_ci

• A coluna A usa agrupamentoutf8mb4_unicode_ci

• Agrupamento usado da coluna Butf8mb4_bin

• A coluna A armazena corretamente letras como é, ä, etc.

• A coluna B os armazena usando seu unicode, por exemplo, \u00e9para é.

Agora alterei o agrupamento da coluna B usando:

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

Portanto, a coluna A e a coluna B agora usam exatamente o mesmo conjunto de caracteres + agrupamento. Os dados inseridos column_bsão sempre um arquivo JSON_OBJECT.

Ainda assim, não importa as alterações, apenas na coluna B, mas não na A, letras como éainda são armazenadas em seu formato codificado em unicode, e apóstrofos também são armazenados como '. Quaisquer letras especiais como ésão recuperadas corretamente ao consultar os dados (se você consultar os dados que contêm sth like l\u00e9ger, você obterá corretamente léger).

MAS, se você consultar sth like l'\u00e9l\u00e9phant, você não obterá l'éléphant, mas l'éléphant.

Estou pegando os dados em PHP, e garantindo que o conjunto de caracteres da conexão também seja utf8mb4, usando mysqli_set_charset( $connection, 'utf8mb4' );.

Eu sei que teoricamente poderia simplesmente codificar uma pesquisa e substituir os dados recuperados; mas por que diabos essa conversão de apóstrofo ainda está acontecendo?

ATUALIZAÇÃO 2

Descobri uma solução para o 'problema (veja minha resposta postada), mas agora estou tentando descobrir uma maneira para o outro problema mencionado: Ao armazenar "Hello, I'm James"em um LONGTEXTcampo de dados, o MariaDB armazena Hello, I\'m James(ele escapa das aspas simples, acho por razões de segurança). Atualmente, quando recupero os dados, recebo

Hello, I\'m James

Mas eu quero pegar

Hello, I'm James

Mesmo que os dados sejam armazenados como

Hello, I\'m James

Claro que você poderia novamente fazer coisas de substituição de pesquisa em PHP, mas eu sinto que deve haver uma abordagem padrão no MariaDB, no lado do banco de dados, para isso ..?

ATUALIZAÇÃO 3

Graças à dica de @Rick James de que nenhuma conversão de conjunto de caracteres de dados deve ocorrer neste caso no lado do servidor de banco de dados, verifiquei meu código do lado do servidor e, de fato, encontrei o primeiro problema relacionado ao problema de caractere unicode ( \u00e9em vez de é, etc.). O motivo foi que, antes de inserir o JSON no meu banco de dados, converti de um array PHP em uma string JSON usando json_encode . O problema é que isso escapa dos meus caracteres unicode \uXXXXpor padrão, o que não deve ser feito nesse caso. Para evitar isso, em vez de:

json_encode( $data )

Usar:

json_encode( $data, JSON_UNESCAPED_UNICODE )

Então isso resolveu tudo relacionado aos erros de codificação unicode. O problema relativo à estranha codificação do apóstrofo para '/ o escape para \'ainda permanece sem solução.

ATUALIZAÇÃO 4

Ok, também encontrei a fonte do 'problema \'e foram causados ​​pelo mesmo problema. Foi porque eu higienizei os dados de string usados ​​para a inserção com:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING
);

ao invés de:

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING,
  FILTER_FLAG_NO_ENCODE_QUOTES
);

Isso nunca mostrou a 'codificação quando você ecoa a string, provavelmente porque ela é convertida de volta para uma aspa simples quando ecoada, mas isso é apenas uma suposição. E sim, fazer o mesmo onde \'em vez de 'foi armazenado também resolveu esse problema. Então acho que é isso.

Na documentação do PG 13, há vários exemplos de agrupamentos de UTI para fins especializados. Também é mencionado que existem localidades ICU que permitem criar agrupamentos para ignorar acentos e que podem ser encontrados em https://github.com/unicode-org/cldr/blob/master/common/bcp47/collation.xml

No entanto, depois de ler esse documento, ainda não tenho certeza sobre qual é a localidade que devo usar para criar um agrupamento ICU para comparações insensíveis ao sotaque em espanhol.

Qual é o nome de tal localidade de UTI? Existe alguma lista de agrupamentos em espanhol da UTI?

Eu tenho um aplicativo onde cada conta de usuário pode definir seu idioma de aplicativo preferido. Eu armazeno essas preferências de idioma como tag de idioma IETF BCP47 (por exemplo, "de", "en-US"). Agora, para consultas desse usuário, quero classificar os resultados com base em sua preferência de idioma.

Imagine eu tendo os dados AAA, TTT, OOO, ÖÖÖ em uma tabela com agrupamento utf8mb4_general_ci.

Se eu quiser que eles sejam classificados para um usuário alemão, posso fazer

SELECT word FROM `words` order by word collate utf8mb4_german2_ci

Que me daria a ordem

AAA, ÖÖÖ, OOO, TTT

Para um usuário islandês, no entanto, usando o agrupamento utf8mb4_icelandic_ci, eu obteria corretamente

AAA, OOO, TTT, ÖÖÖ

Agora, na minha aplicação eu não salvo utf8mb4_icelandic_ciou utf8mb4_german2_cipara aquele usuário, mas eu salvo isou de-DE.

Existe uma maneira de enviar esta tag de idioma isou de-DEBCP47 diretamente para o MariaDB para especificar o agrupamento em vez do nome do agrupamento? Ou eu teria que ter um mapeamento entre a marca de idioma e o nome da ordenação em meu aplicativo para enviar as informações de ordenação corretas para o usuário?

Parece haver algo especial sobre 'tR' no agrupamento vietnamita. Appreicate se alguém que sabe sobre isso pode explicar em termos simples. Esse problema foi descoberto durante a instalação do nosso produto em um SQL Server agrupado "vietnamita". Uma das tabelas no esquema tem 'tR' em seu nome, mas um procedimento armazenado está referenciando a tabela em todas as letras minúsculas 'tr'. E esta referência falha.

Acho que essa situação é análoga a '阝' combinando 'ss' em outros agrupamentos.

Aqui está uma reprodução:

select  case when 'tr' = 'tR' COLLATE SQL_Latin1_General_CP1_CI_AS   then 'match' else 'no match' end 
select  case when 'tr' = 'tR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 
select  case when 'tr' = 'TR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 

Resultados:

-----
match


--------
no match


-----
match

O segundo T-SQL produz a incompatibilidade. Outras combinações de 't' e 'R' não.

A partir do SQL Server 2019, ele oferece suporte a UTF-8 como agrupamento. No entanto, de acordo com as seguintes consultas:

SELECT COLLATIONPROPERTY('Arabic_100_CS_AS_KS_WS_SC_UTF8', 'CodePage')
SELECT COLLATIONPROPERTY('Latin1_General_100_CS_AS_KS_WS_SC_UTF8', 'CodePage');

ambos retornam a página de código 65001que é Unicode no Windows. Além disso, todos os novos _UTF8agrupamentos usam a página de código 65001:

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE '%_UTF8';

Existem diferenças entre usar Arabic_100_CS_AS_KS_WS_SC_UTF8e Latin1_General_100_CS_AS_KS_WS_SC_UTF8como agrupamento?

Estou com um problema com um select case when statement, gostaria de adicionar uma nova coluna no select case when statement, obtive o resultado mas com ?????porque configurei a coluna para palavras arábicas, tentei converter o conteúdo de a nova coluna nvarchar(55)infelizmente, obtive o mesmo resultado.

Como posso obter o resultado correto?

SELECT case when ( CAST(o.startTime as time(7)) > cast(Start as time(7))) 
          then  cast('قدوم' as nvarchar(55))
          else cast('رجوع' as nvarchar(55)) end as stat,
       userId, FirstName
from   Users, TimeTable