DevelJoe's questions

Estou usando o MariaDB 10.3, e o que estou tentando fazer é estabelecer um relacionamento 1-para-n conforme especificado por exemplo aqui na seção "Relacionamento um-para-muitos". Estou tentando fazer isso via:

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL DEFAULT ID
);

CREATE TABLE related_table (
  ID INT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
)

O SQL Fiddle Editor etc. falha na execução disso, com o erro:

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of 'ID'

Portanto, suponho que você não possa definir o valor padrão de uma coluna igual a outra coluna, se o valor dessa outra coluna for definido por uma função ou expressão?

Se eu omitir o AUTO_INCREMENT, obtenho:

Can't create table "fiddle"."related_table" (errno: 150 "Foreign key constraint is incorrectly formed")

Portanto, presumo que você não possa criar restrições FOREIGN KEY com base em colunas referenciadas com um valor padrão?

Você vê outra possibilidade de fazer o que preciso, que é:

A) Quando insiro um novo registro em test_tablee não forneço test_value, seu valor deve ser igual à chave primária do registro inserido, que deve ser gerada automaticamente.

B) O related_table's IDdeve referir-se ao test_table's test_valuecomo chave estrangeira.

A única solução alternativa que vejo atualmente é:

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL
);

CREATE TABLE related_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
);

E então, se eu inserir, por exemplo, 3 registros em test_table:

1. Insira o primeiro novo registro em test_tablee pegue o arquivo LAST_INSERT_ID.
2. Atualize o primeiro registro recém-criado definindo o valor dele test_valueigual ao retornado LAST_INSERT_ID.
3. Insira os dois registros restantes em test_table, desta vez diretamente com o LAST_INSERT_IDretornado de antes como o valor de test_value.
4. Insira um novo registro related_tablecom o valor do retornado LAST_INSERT_IDcomo seu IDcampo de chave primária.

Mas isso parece complicado demais. Não existe uma solução melhor para este caso de uso?

Decidi mudar do gerenciamento de sessão nativo do PHP baseado em $_SESSIONusar o meu próprio, devido a muitos problemas diferentes que tive até agora com as possibilidades do PHP e também porque estou construindo uma API que deve ser compatível com solicitações de aplicativos móveis ; então eu não posso confiar de $_SESSIONqualquer maneira. Eu sei que existem frameworks como symfony etc. por aí para isso, mas usar sth assim para essas operações de bebê seria um exagero.

O que estou implementando no momento é, portanto, de acordo com essa lógica ( source ):

O último ponto que estou querendo saber é que o Redis / o artigo vinculado recomenda "Armazenar informações da sessão e do usuário " após uma primeira autenticação bem-sucedida, como um login ou similar. No entanto, acho que seria muito mais benéfico não fazer isso e armazenar apenas o identificador da sessão, como um cartão VIP que permitirá ao cliente lançar solicitações na área segura do aplicativo da web.

Meu pensamento foi apenas isso:

• a maioria das informações do usuário em questão está no banco de dados de qualquer maneira e de forma não criptografada; a maior parte mesmo em uma única tabela de banco de dados.
• Eu acho que geralmente é uma má prática armazenar os mesmos dados em vários locais de um banco de dados, mesmo que um seja armazenado para acesso "mais rápido" ou "facilitado" nesse sentido.
• Todas as informações do usuário (= todos os dados necessários para todas as solicitações REST possíveis da plataforma, reunidas em um JSON) abrangem cerca de 50 pares de valores-chave, e alguns desses valores são criptografados, então eu também precisaria criptografar o usuário resultante info , resultando em enormes operações de criptografia / descriptografia a cada solicitação (e carregamento de página, como também usado para autenticação), e uma coluna adicional de sth like VARBINARY(600).
• Para uma solicitação, nunca preciso de mais de 10 desses dados de kv e, principalmente, apenas coisas como 2 - 3.

Dadas as considerações acima, eu me perguntei: não é um exagero completo e uma enorme transferência de dados para nada, armazenar todas as informações do usuário (soma de todos os dados do usuário que preciso em todas as solicitações REST da plataforma) ao longo do identificador de sessão para imitar uma sessão? Não é melhor apenas armazenar o identificador de sessão no banco de dados e consultar apenas as informações necessárias do usuário quando a solicitação real chegar (etapa 4a da img acima)? Isso não faz muito mais sentido?

Sinto vontade de desperdiçar tantos recursos se armazenar todas as informações do usuário junto com o identificador de sessão. Como isso também significa que eu estaria descriptografando e enviando uma quantidade significativa de carga útil em vão para diferentes solicitações.

Então, qual é a melhor prática aqui, armazenar as informações do usuário na autenticação (etapa 1b) ou recuperá-las quando a autenticação for uma solicitação (etapa 4b) ??

Para lhe dar uma ideia; a compensação resultante precisaria ser feita entre:

• descriptografar os dados e enviar muito mais informações do usuário do que realmente necessário pelo endpoint, a cada solicitação

VS

• fazendo pelo menos 2 JOINS em PRIMARY KEYs em cada carregamento / solicitação de página, em média, e, eventualmente, 1-2 descriptografias menores nessa consulta também.

Em termos de desempenho, não tenho ideia do que é mais inteligente aqui.

E outra compensação é a conveniência de codificação, pois não armazenar todo o conjunto de dados da sessão como tal no banco de dados significa uma solicitação específica do banco de dados de informações do usuário por chamada REST.

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.

É a primeira vez que estou usando criptografia no MariaDB, então preciso garantir que estou acertando. Eu preciso simplesmente armazenar alguns identificadores de forma criptografada e estou me perguntando se estou fazendo isso corretamente. Como estou recebendo alguns comportamentos inesperados na recuperação de dados (às vezes apenas), estou suspeitando que há algo errado (e estive verificando com os documentos etc., tudo parece certo ..?).

• Estou armazenando os dados em BLOBe NOT NULLcolunas, nada mais especificado.
• Estou inserindo os dados usando isso, enquanto keyé gerado viaopenssl rand -base64 32

INSERT INTO data_table ( encr_data ) VALUES( AES_ENCRYPT( "secret_string", "key" ) );

• Estou recuperando os dados usando isso:

SELECT CAST( AES_DECRYPT( encr_data, "key" ) AS CHAR ) as encr_data FROM data_table;

Essa é a maneira correta de fazer isso no MariaDB? A sequência secreta consiste em cerca de 35 a 40 caracteres.

É realmente fundamental para mim, pois o aplicativo que estou codificando está armazenando alguns dados secretos para serem usados ​​com outra API. Em outras palavras, a criptografia não deve trazer nenhum risco quanto à integridade dos dados em si. Apenas o pensamento de precisar regenerar todos os dados criptografados devido a qualquer erro de criptografia (tornando os dados criptografados + armazenados inutilizáveis) ...

Portanto, preciso garantir que nenhum dado seja cortado da string criptografada e que a criptografia seja feita corretamente, daí o motivo dessa pergunta.

Embora essa pergunta seja muito semelhante, não acho que seja também a melhor abordagem para o meu caso, embora pareça funcionar. Considere o seguinte exemplo:

CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

Agora quero criar UMA consulta de atualização que esconda todas as mensagens do usuário xpara ele; ou seja, que define show_for_writerigual a 0 onde o writeré x, E que define show_for_recipientigual a 0 onde o recipienté x. Observe que quero tornar isso o mais eficiente possível, pois esta tabela pode conter milhões de registros (um por mensagem) para inúmeras combinações de escritor-destinatário (índice composto, não exclusivo).

O que tentei fazer, de acordo com o post vinculado, é:

UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

No entanto, sinto que isso não é o ideal, porque passa por todos os registros da tabela, e essa consulta pode não ser feita em escala regular, mas também não raramente. Qual a melhor solução para isso? Estou aberto a qualquer solução, até mesmo propostas de modificação de colunas estruturais; Estou usando o MariaDB 10.3.

Uma ideia que tive foi talvez editar a estrutura da seguinte forma:

CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

E faça algo como:

UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

O problema aqui é que eu consulto as mensagens se elas devem ser mostradas para 'x' na inicialização. Portanto, definir showum JSONtipo de dados não possibilitará indexar essa coluna, portanto, consultar em f( show) de forma eficiente. Por isso achei que essa ideia pode não ser a ideal, mas apenas para mostrar que estou basicamente aberto até mesmo a modificações no nível estrutural.

Caso contrário, eu poderia simplesmente disparar duas UPDATEinstruções subsequentes, assim:

UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

Mas eu realmente prefiro usar uma consulta por ação. Ajuda por favor!

Confira o exemplo aqui, com a consulta de atualização a ser otimizada para evitar passar por cima de todos os registros da tabela:

http://www.sqlfiddle.com/#!9/f6830d/1

RDBMS: MariaDB 10.3

Digamos que você tenha uma tabela que armazena informações específicas ( info_1, info_2, etc.) de acordo com um determinado par de nomes de usuário; igual a:

+----------+----------+----------+----------+----------+------+--..
|  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+----------+----------+----------+----------+----------+------+--..

onde os valores de user_1e user_2cada igual a um nome de usuário específico e distinto ( VARCHAR(50)), e são definidos como índice de várias colunas (user_1,user_2) da tabela na criação. As consultas que meu aplicativo faz pesquisa em função de:

A) um determinado par de usuários:

WHERE user_1 = name_1 AND user_2 = name_2 OR user_1 = name_2 AND user_2 = name_1

B) um determinado usuário/retorno todos os registros onde um determinado usuário é um dos membros:

WHERE user_1 = name_1 OR user_1 = '%' AND user_2 = name_1

Problema: Devo garantir que as pesquisas sejam sempre indexadas. Ao usar uma ORcláusula, uma varredura completa da tabela é executada a cada vez.

SOLUÇÃO I)

Graças a esta resposta, as possíveis consultas que resultam em pesquisas indexadas para esses casos são:

A)

SELECT * FROM my_table WHERE user_1 = name_1 AND user_2 = name_2
UNION ALL
SELECT * FROM my_table WHERE user_1 = name_2 AND user_2 = name_1;

B)

SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_1 = '%' AND user_2 = name_1;

Porém com a desvantagem de sempre usar duas SELECTinstruções e uma WHEREcláusula que sempre será em vão (pois sempre haverá um registro por par de usuário). É por isso que tentei otimizar ainda mais a consulta usando um índice de texto completo , o que resultou na seguinte tabela:

SOLUÇÃO II)

+---------+----------+----------+----------+------+--..
|  users  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+---------+----------+----------+----------+------+--..

onde usersfoi definido como FULLTEXT VARCHAR(150), e seu valor sempre corresponde ao formato name_1-name_2, enquanto eu usei um hífen porque uma vírgula é usada na MATCH...AGAINSTsintaxe para executar consultas de índice de texto completo.

Com essa estrutura, agora obtenho as seguintes novas possibilidades de consulta para A) e B):

A)

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"');

B)

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('name_1');

O que na minha opinião facilita drasticamente a estrutura da consulta, e não usa nem duas SELECTdeclarações, nem uma WHEREcláusula adicional que será em vão. Ainda assim, como ainda não sei muito sobre otimização de desempenho de consulta e a comparação de pesquisas de índice de texto completo com pesquisas de índice regulares, gostaria de saber qual solução de I e II pode ter melhor desempenho e por quê? Ou existe ainda uma abordagem melhor?