Perguntas[mysql](coding)

Digamos que eu tenha a seguinte estrutura:

Cursos

Estudantes

Quero fazer uma consulta que liste todos os alunos de um curso, mas filtre os cursos se um aluno específico estiver naquele curso.

Se eu usar a seguinte consulta, o aluno "Bob" não estará na lista de alunos separados por vírgulas.

SELECT 
  c.name, 
  GROUP_CONCAT(s.name separator ',') 
FROM 
  Courses as c 
  LEFT JOIN Students s ON s.courseID = c.ID 
WEHRE 
  s.name = 'Alice'
GROUP BY
  c.name

Sei que posso me juntar duas vezes e usar uma para fazer a filtragem e a outra para o GROUP_CONCAT, mas parece ineficiente porque estou me juntando duas vezes. Existe uma maneira mais eficiente e/ou direta de fazer isso?

Quando o e-mail de um usuário é confirmado, um valor de TIMESTAMP do MySQL é atualizado e seu valor não deve ser igual a '1970-01-01 00:00:01' (PADRÃO). Estou tentando criar uma restrição do MySQL que imponha isso. Portanto, se o usuário tiver o seguinte:
confirmed=1
email_confirmed_time='1970-01-01 00:00:01'
... então a restrição é violada. Mas se confirmed=0 com o mesmo TIMESTAMP, então ela é aceita.

O que há de errado com o meu comando table abaixo? Ele me informa que há uma violação em CHK_emailConfirmedTime_when_confirmed se eu inserir o seguinte:

insert into user (username, `password`, first_name, last_name, `role`, `group`, logged_in, confirmed, approved, email_confirmed_time) values 
    ("0", "0", "joe", "dill", "{admin, user, guest}", "{create, read, update, delete}", 0, 0, 0, DEFAULT)
;

CREATE TABLE IF NOT EXISTS user (
    `id` SERIAL PRIMARY KEY,
    `username` varchar(75) NOT NULL,
    `password` varchar(100) NOT NULL,
    `first_name` varchar(50) NOT NULL,
    `last_name` varchar(50) NOT NULL,
    `role` varchar(25) NOT NULL DEFAULT "{guest}",
    `group` varchar(50) NOT NULL DEFAULT "{read}",
    `logged_in` int NOT NULL DEFAULT 0,
    `confirmed` INTEGER NOT NULL DEFAULT 0,
    `approved` INTEGER NOT NULL DEFAULT 0,
    `email_confirmed_time` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
    CONSTRAINT CHK_confirmed CHECK (`confirmed`=0 OR `confirmed`=1),
    CONSTRAINT CHK_approved CHECK (`approved`=0 OR `approved`=1),
    CONSTRAINT CHK_emailConfirmedTime_when_notConfirmed CHECK (`confirmed`=0 AND `email_confirmed_time`=TIMESTAMP('1970-01-01 00:00:01')),
    CONSTRAINT CHK_emailConfirmedTime_when_confirmed CHECK (`confirmed`=1 AND `email_confirmed_time`>TIMESTAMP('1970-01-01 00:00:01'))
);

Tenho uma consulta com baixo desempenho e não sei onde está o problema. Uma versão bastante reduzida da consulta, que ainda apresenta o mesmo problema, é:

SELECT members.id, 
FROM members
LEFT JOIN ( SELECT 
  member_id,
  GROUP_CONCAT(hobby)
  FROM member_hobbies
  GROUP BY member_id) AS hobbies
ON member.id=hobbies.member_id

Há um índice primário em members.ide um índice correspondente em hobbies.member_id.

Se eu remover o GROUP_CONCATa consulta será cerca de 50 vezes mais rápida.

Estou ciente de que o exemplo dado acima pode ser simplificado e não requer a subconsulta, e estou tentando simplificar minha consulta completa.

Gostaria de saber se é possível otimizar a consulta em seu formato atual e onde está o problema.

No momento, estou em Londres no horário BST, GMT + 1, o horário, por exemplo, é 20h, horário local.

Quando adiciono uma entrada na tabela, o registro de data e hora é definido automaticamente. Quando visualizo no phpMyAdmin, ele mostra a hora local atual corretamente.

Quando executo esta consulta, ela retorna o item inserido recentemente.

SELECT * FROM products WHERE added_date > '2025-04-05T20:00:00Z'

Mas isso não faz sentido para mim. 20h em Zulu são 21h no horário local.

Tenho 2 tabelas:

comments_table

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`media_id` int(11) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT NULL,//user who commented
`title` varchar(300) DEFAULT NULL,
`url` varchar(300) DEFAULT NULL,
`c_date` datetime,
`comment` longtext DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`)

id         comment    user_id
1         foo             1
2         bla             2  
3         something       7 

votes_table

`comment_id` int(11) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT NULL,//user who voted
`vote` tinyint(1) DEFAULT 0,
INDEX `comment_id` (`comment_id`)

comment_id user_id vote
1         2       1
2         4       -1  
3         7       0
3         1       1
2         1       1 

Tenho 2 dúvidas (obter todos os comentários):

$comments = $wpdb->get_results($wpdb->prepare("SELECT ct.id, ct.comment, ct.user_id, ct.user_display_name, ct.avatar, ct.c_date, SUM(DISTINCT vt.vote) AS vote
                    FROM $comments_table as ct 
                    LEFT JOIN $votes_table vt on ct.id = vt.comment_id 
                    WHERE ct.media_id=%d 
                    GROUP BY ct.id
                    ORDER BY ct.c_date DESC", $media_id), ARRAY_A);

Obter comentários onde o usuário votou:

$user_votes = $wpdb->get_results($wpdb->prepare("SELECT vote, comment_id
                    FROM $votes_table
                    WHERE user_id=%d", $user_id), ARRAY_A);

Isso funciona como esperado. Há como fazer isso em uma consulta? (por motivos de desempenho)

Por exemplo, adicione aos resultados user_vote = votes_table.vote a comment_table.id votes_table.user_id votado.

Dadas as tabelas configuradas como esta

    tableA {
    id = 1
    name = 'bob'
    id = 2
    name = 'sally'
    id = 3
    name = 'sue'
    }
    tableB {
    id = 1
    name = 'bob'
    }

Se eu executar este comando, ele retornará os IDs 2 e 3, conforme desejado:

select id 
from tableA a 
left join tableB b using (id) 
where id not in (select id from tableB) 
order by id 
limit 10;

Mas é uma tabela muito grande, então preciso usar offset. Quando tento o comando a seguir, nenhum resultado é retornado.

select id 
from tableA a 
left join tableB b using (id) 
where id not in (select id from tableB) 
order by id 
limit 10 offset 10;

Existe uma maneira de usar offset quando uma junção está sendo usada?

Quero carregar um arquivo csv no banco de dados MySQL. Este arquivo contém alguns campos na formatação de campos Postgres, por exemplo, booleano é 't'. No banco de dados MySQL, esses campos têm o tipo TINYINT(1) como seu booleano também. Então, o que eu quero fazer é converter o 't' do Postgres em 1 imediatamente, enquanto executo a instrução LOAD DATA INFILE.

Existe uma maneira de fazer isso ou preciso pré-processar o arquivo antes de poder usá-lo dentro do sql? Os arquivos que preciso processar são um pouco grandes, então quero evitar operações de arquivo adicionais neles.

LOAD DATA 
    INFILE '/load-data/photos.txt'
    INTO TABLE photos
    FIELDS TERMINATED BY '\t';

Tenho uma tabela - Lead.Metadata- com uma coluna chamada metaque é do tipo JSON e uma coluna gerada:

cycle VARCHAR(255) AS (JSON_UNQUOTE(meta->"$.cycle"))

O problema é que quando faço isso UPDATE Lead.Metadata SET cycle = 'MONTHLY'recebo este erro:

Código de erro: 3105. O valor especificado para a coluna gerada 'cycle' na tabela 'Metadata' não é permitido.

Não entendo. ciclo é um VARCHAR(255)e 'MONTHLY'deve se encaixar nisso.

Alguma ideia?

(além disso, para sua informação, a base de código é uma base de código legada; a estrutura do banco de dados não é ótima, mas consertar isso é um projeto maior)

Esta é a situação

select calldate,src,dst,clid,nome from cdr limit 255 offset 1015;
+---------------------+------------+--------------+------------------------------------+------+
| calldate            | src        | dst          | clid                               | name |
+---------------------+------------+--------------+------------------------------------+------+
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:07 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:05 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:05 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:31:53 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 16:22:08 | 3233333    | 9182374747   | NONE                               | CKTC |
| 2025-03-04 18:41:46 | 3233333    | 9182374747   | NONE                               | CKTC |
| 2025-03-08 01:11:27 | 3233333    | 9182374747   | NONE                               | CKTC |
+---------------------+------------+--------------+------------------------------------+------+

Quero substituir +11 por nada, em palavras simples: quero cortá-lo, mas apenas +11, sem tocar em outros valores.

Se eu usar esta linha

select calldate,src,substr(dst,4,11) as dst,clid,nome from cdr limit 255 offset 1015;

Cortou todos os valores, não só +11

Se eu usar este

select calldate,src,(select substr(dst,4,11) where dst='+11123456789') as dst,clid,nome from cdr limit 255 offset 1015;

funciona bem, mas os valores "dst" de outros números se tornaram NULL Minha ideia é um resultado como este

select calldate,src,dst,clid,nome from cdr limit 255 offset 1015;
    +---------------------+------------+--------------+------------------------------------+------+
    | calldate            | src        | dst          | clid                               | name |
    +---------------------+------------+--------------+------------------------------------+------+
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:07 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:05 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:05 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:31:53 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 16:22:08 | 3233333    | 9182374747   | NONE                               | CKTC |
    | 2025-03-04 18:41:46 | 3233333    | 9182374747   | NONE                               | CKTC |
    | 2025-03-08 01:11:27 | 3233333    | 9182374747   | NONE                               | CKTC |
    +---------------------+------------+--------------+------------------------------------+------+

, como fazer? É possível?

Tenho uma consulta que funcionou sem erros até o mês passado.

SELECT ..., (SELECT count(id) FROM table2) as rows FROM table1;

Agora o MySQL gera este erro:

Erro de sintaxe ou violação de acesso: 1064 Você tem um erro na sintaxe do seu SQL; verifique o manual correspondente à versão do seu servidor MySQL para saber a sintaxe correta a ser usada perto de 'rows [...]

É por isso que rowsse tornou uma palavra reservada no MySQL?

Eu executo a consulta em uma máquina que não posso verificar e/ou controlar, então não sei se a causa pode ser uma atualização de software.