32bitfloat's questions

Eu tentei a criptografia aes usando a seguinte declaração:

SELECT encrypt('test', 'key', 'aes');

que funcionou, mas não consigo descriptografar o valor. Inseri-o em um campo do tipo de dados bytea mas não tenho certeza se foi o caminho certo.

SELECT decrypt(pw, 'key', 'aes') FROM table WHERE ID = 1;

me dá o erro

ERRO: função decrypt(bytea, desconhecido, desconhecido) não existe
LINHA 1: SELECT decrypt(pw, 'chave', 'aes') FROM tabela WHERE ID = 7; ^
DICA: Nenhuma função corresponde ao nome e aos tipos de argumento fornecidos. Talvez seja necessário adicionar conversões de tipo explícitas.

Isso realmente significa que encrypt() é uma função existente, mas não decrypt()? De que outra forma eu poderia recuperar valores criptografados com aes?

Sou novo no PostgreSQL e tive problemas ao usar a seguinte consulta:

SELECT * FROM table1 WHERE ID = 1

ERRO: a coluna "id" não existe LINHA 2: WHERE ID = 1

onde ID é uma coluna que adicionei por meio de uma ferramenta GUI.

Então descobri que ID é uma palavra- chave na minha versão atual do PSQL e deve ser citado. Mas não consigo reconhecer para que serve esse ID.

Na página vinculada, há o seguinte aviso:

É importante entender antes de estudar a Tabela C-1 que o fato de uma palavra-chave não estar reservada no PostgreSQL (==true) não significa que o recurso relacionado à palavra não esteja implementado.

Portanto, ID não está marcado como uma palavra-chave do PostgreSQL e parece haver um recurso conectado a ele, mas não consigo descobrir qual. O próprio SELECT ID se transforma na mesma mensagem de erro postada acima.

Sou muito novo em gatilhos e tive problemas gerais. Eu gostaria de conseguir isso:

CREATE TABLE `searcharticles` (
 `articleID` int(11) unsigned NOT NULL,
 `ean` char(13) COLLATE utf8_unicode_ci DEFAULT NULL,
 `manufacturerNumber` varchar(20) COLLATE utf8_unicode_ci NOT NULL,  
 PRIMARY KEY (`articleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `addresses` (
 `articleID` int(11) unsigned NOT NULL,
 `shop` enum('shop1','shop2') COLLATE utf8_unicode_ci NOT NULL,
 `url` varchar(255) COLLATE utf8_unicode_ci NULL,
 `status` tinyint(3) NOT NULL DEFAULT '0',
 `createTime` datetime NOT NULL,
 `updateTime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`articleID`,`shop`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

Quando uma nova linha é inserida em searcharticles , os endereços da tabela devem ser preenchidos, mas não apenas uma vez. Eu gostaria de fazer um registro para cada loja que está em enum shop(sei que isso não é bem projetado). Estou preso nisso:

CREATE TRIGGER ins_addresses AFTER INSERT on searcharticles
FOR EACH ROW
BEGIN
  INSERT INTO addresses(articleID, shop, createtime) VALUES(NEW.articleID, ?, CURRENT_TIMESTAMP);
END

Como eu poderia iterar pelos possíveis valores de enumeração para acionar um insert com 'shop1' e outro com 'shop2' sem escrever um possível nome de loja no insert?

Posso imaginar que seja realmente mais importante explicar as instruções SELECT, mas me pergunto por que não há possibilidade de usar EXPLAIN em UPDATEs/INSERTs. Posso usar uma instrução WHERE em um UPDATE onde os índices estão sendo usados ​​(eu acho?), Deve ser possível ver se eles são realmente usados. Além disso, no caso de usar uma instrução SELECT dentro de um UPDATE, também não posso usar a explicação para esta parte sem obter um erro de sintaxe.
Eu entendi mal algo sobre os processos em UPDATEs e INSERTs? De que outra forma posso depurar essas declarações?

Estou um pouco irritado por causa de alguns avisos no mysql errorlog. Exemplo 1:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
The statement is unsafe because it uses a LIMIT clause. 
This is unsafe because the set of rows included cannot be predicted. 
Statement: UPDATE `table` SET `status` = '1' WHERE `ID` = '15800' LIMIT 1

Meu colega escreveu essas declarações e sempre me perguntei sobre o propósito de limitar uma declaração ao usar ID explícito. No entanto, este aviso também ocorre por não usar LIMIT.
Exemplo 2:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
The statement is unsafe because it uses a LIMIT clause. 
This is unsafe because the set of rows included cannot be predicted. 
Statement: DELETE FROM table WHERE id = 426888

O que pode estar errado neste caso?

Imagine a seguinte situação:

A Tabela A usa MyISAM e contém 4 campos (texto) com um índice FULLTEXT combinado.

FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4

A Tabela B usa InnoDB e contém alguns outros campos, onde 5 deles são indexados como 'ORD'.

ORD order1 | order2 | order3 | order4 | order5

Eu gosto de fazer uma pesquisa de texto completo na tabela A juntando a tabela B em seu ID estrangeiro e ordenando os resultados pelas colunas indexadas da tabela B.

Query1 - corresponde a todas as 4 colunas de texto completo:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

leva 1,6565 segundos.

EXPLICAR Consulta1:

select_type  table   type     possible_keys     key      key_len  ref   rows            Extra
SIMPLE       tableA  fulltext PRIMARY,FULLTEXT  FULLTEXT    0     1                     Using where; Using temporary; Using filesort
SIMPLE       tableB  eq_ref   PRIMARY           PRIMARY     4     db.tableA.tableB_id   1 

nenhum índice é usado, a tabela temporária era necessária. Não sei o que significa "Extra 1" na segunda linha.

Query2 - corresponde apenas a 3 colunas:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

demora 0,0114 segundos.

EXPLICAR Consulta2:

select_type table   type    possible_keys  key     key_len  ref           rows  Extra
SIMPLE      tableB  index   PRIMARY        ORD     783      NULL          15    Using index
SIMPLE      tableA  eq_ref  PRIMARY        PRIMARY 4        db.tableB.id  1     Using where

A ordem das tabelas listadas foi alterada. Para tableB, o índice foi usado, para tableA nenhuma tabela temporária foi necessária.

Não importa qual coluna de texto completo eu excluo da consulta - desde que eu corresponda a menos de 4 delas, obtenho a duração e a explicação da Consulta2 exibida.
Talvez interessante: a quantidade de linhas é igual nas duas tabelas, cerca de 180k.

Gostaria de saber o motivo desse comportamento. Quero dizer, parece que todo o modo de execução dependeria do número de colunas correspondentes.

edit: agora estou completamente confuso. Eu apaguei o arquivo fulltext-index. Eu correspondi a todas as 4 (ex-) colunas de texto completo (Consulta1). Agora leva 0,1205 segundos. EXPLAIN mostra que nenhuma tabela temporária é necessária; mas eu me pergunto como posso combinar mais rápido no modo booleano sem ter um índice de texto completo.

Não consigo que a pesquisa de texto completo funcione como gostaria e não entendo as diferenças nas listas de resultados.

Exemplos de declarações:

SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE '%punkt%'

retorna 92 ​​linhas. Recebo linhas que têm correspondências, por exemplo, como "Punkten", "Zwei-Punkte-Vorsprung" e "Treffpunkt" na coluna meldungstext.

Eu defini um fulltext-index na coluna "meldungstext" e tentei isto:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*')

isso retorna apenas 8 linhas. Eu recebo apenas linhas que correspondem ao próprio "Punkt" ou palavras que eu acho que são consideradas "Punkt" como em "i-Punkt".

Eu então tentei o modo booleano:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*' IN BOOLEAN MODE)

retorna 44 linhas. Recebo linhas que têm "Zwei-Punkte-Vorsprung" ou "Treffpunkt" na coluna meldungstext, mas não aquelas com "Punkten".

Por que isso acontece e como posso definir uma pesquisa de texto completo "totalmente" funcionando para evitar o uso de LIKE '%%' na cláusula where?

Encontramos um problema após mover o banco de dados de nosso cliente para um servidor extra. Isso deveria ter tido efeitos positivos no desempenho do site, mas há um problema com o bloqueio de tabelas no MyISAM. (Já ouvi falar do uso do InnoDB em vez do MyISAM, mas não podemos alterar o mecanismo em um futuro próximo).
Podemos identificá-lo em uma consulta de atualização que é realizada quando um moderador ativa um comentário no site de artigos. Este é o processo:

• a consulta de atualização é processada SET status = 1 WHERE id = 5(o índice é definido)
• os arquivos em cache da página são excluídos

Neste ponto, a página inteira fica lenta. O próprio banco de dados está ocupado por minutos. Eu busquei a lista de processos algumas vezes e vi cerca de 60 entradas de diferentes consultas de seleção, que estavam todas no estado aguardando o bloqueio de nível de tabela .

1. Não entendo por que essa atualização na tabela article_commentspode afetar as instruções select para que articlea tabela aguarde o bloqueio no nível da tabela. Na processlist quase todas as consultas em espera eram desta tabela. Eu li sobre o fato de que atualizações/inserções são preferidas a seleções e que isso pode causar tais problemas, mas a tabela de artigos em si não é atualizada quando os comentários são ativados, então as seleções não devem esperar. Eu entendi errado isso?
2. Existe algo além de mudar para o InnoDB para evitar esse comportamento ou pelo menos para obter um equilíbrio melhor? Estou muito irritado com o fato de que esse problema não apareceu antes de mover o banco de dados para o novo servidor. Acho que há alguma configuração errada, mas não sei como identificar.