主页 / coding / 问题

问题[mysql](coding)

Martin Hope
Cave Johnson
Asked: 2025-04-24 10:01:50 +0800 CST
  • 5

假设我有以下结构：

课程

ID 姓名
1 数学
2 历史

学生

ID 姓名 课程编号
1 鲍勃 1
2 爱丽丝 1

我想进行查询，列出一门课程中的所有学生，但如果特定学生参加了该课程，则过滤课程。

如果我使用以下查询，学生“Bob”不在以逗号分隔的学生列表中。

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

我知道我可以连接两次，一次用于过滤，另一次用于GROUP_CONCAT，但感觉效率很低，因为我连接了两次。有没有更高效、更直接的方法来做到这一点？

mysql
Martin Hope
chocalaca
Asked: 2025-04-20 02:03:31 +0800 CST
  • 8

当用户的电子邮件确认后，mysql 时间戳值会更新，并且其值不应等于“1970-01-01 00:00:01”（默认值）。我正在尝试创建一个 mysql 约束来强制执行此操作。因此，如果用户有以下内容：
confirmed=1
email_confirmed_time='1970-01-01 00:00:01'
...则违反了该约束。但如果 confirmed=0且时间戳相同，则表示该消息被接受。

下面的表格命令有什么问题？如果我插入以下内容，它会告诉我CHK_emailConfirmedTime_when_confirmed存在违规：

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'))
);
mysql
Martin Hope
Dom
Asked: 2025-04-15 22:28:46 +0800 CST
  • 5

我有一个查询性能很差，我不知道问题出在哪里。下面是一个大大简化的查询版本，但仍然显示相同的问题：

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

上有一个主索引members.id， 上有一个匹配索引hobbies.member_id。

如果我删除它，GROUP_CONCAT查询速度将提高约 50 倍。

我知道上面给出的示例可以简化并且不需要子查询，并且我正在尝试简化我的完整查询。

我想知道是否有可能以当前形式优化查询以及问题出在哪里。

mysql
Martin Hope
seventeen
Asked: 2025-04-06 03:44:30 +0800 CST
  • 7

我目前在伦敦，处于 BST（GMT + 1）时间，例如当地时间晚上 8 点。

当我在表中添加一个条目时，时间戳会自动设置，当我在 phpMyAdmin 中查看时，它会正确显示当前本地时间。

当我运行此查询时，它会返回最近插入的项目。

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

但这对我来说毫无意义。祖鲁时间晚上 8 点是当地时间晚上 9 点。

mysql
Martin Hope
Toniq
Asked: 2025-04-04 04:21:01 +0800 CST
  • 4

我有两张桌子：

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

我有 2 个疑问（获取所有评论）：

$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);

获取用户投票的评论：

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

这两个都按预期工作。是否可以在一个查询中完成这些操作？（出于性能原因）

例如，将结果 user_vote = votes_table.vote 添加到 comment_table.id votes_table.user_id 投票。

mysql
Martin Hope
user3052443
Asked: 2025-04-03 23:13:20 +0800 CST
  • 4

给定像这样的表格设置

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

如果我运行此命令，它会返回 ID 2 和 3，正如我所希望的那样：

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

但这是一个非常大的表，所以我需要使用偏移量。当我尝试以下命令时，没有返回任何结果。

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;

使用连接时有没有办法使用偏移？

mysql
Martin Hope
RomanKovalev
Asked: 2025-04-03 01:49:45 +0800 CST
  • 5

我想将 csv 文件加载到 MySQL db 中。此文件包含一些 Postgres 字段格式的字段，例如布尔值为“t”。在 MySQL db 中，这些字段的布尔值也是 TINYINT(1) 类型。因此，我想要做的是在运行 LOAD DATA INFILE 语句时动态将 Postgres“t”转换为 1。

有办法做到这一点吗？还是我需要预处理文件才能在 SQL 中使用它？我需要处理的文件有点大，所以我想避免对其执行额外的文件操作。

LOAD DATA 
    INFILE '/load-data/photos.txt'
    INTO TABLE photos
    FIELDS TERMINATED BY '\t';
mysql
Martin Hope
neubert
Asked: 2025-03-25 01:44:49 +0800 CST
  • 5

我有一张表 - Lead.Metadata- 其中有一个名为metaJSON 类型的列和一个生成的列：

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

问题是当我这样做时UPDATE Lead.Metadata SET cycle = 'MONTHLY'出现此错误：

错误代码：3105。表“元数据”中生成的列“cycle”指定的值是不允许的。

我不明白。循环是一个VARCHAR(255)并且'MONTHLY'应该适合那里。

有什么想法吗？

（此外，请注意，代码库是遗留代码库；数据库结构不是最优的，但修复这个问题是一个更大的项目）

mysql
Martin Hope
elbarna
Asked: 2025-03-08 14:35:15 +0800 CST
  • 6

情况就是这样

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 |
+---------------------+------------+--------------+------------------------------------+------+

我想用空值替换 +11，简而言之：我想将其剪切掉但只替换 +11，而不触及其他值。

如果我用这行

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

它削减了所有值，而不仅仅是 +11

如果我用这个

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

工作正常，但其他数字的“dst”值变为NULL我的想法是这样的

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 |
    +---------------------+------------+--------------+------------------------------------+------+

，怎么办？可能吗？

mysql
Martin Hope
user2342558
Asked: 2025-03-08 00:18:52 +0800 CST
  • 4

我有这个查询，直到上个月为止都没有错误。

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

现在 MySQL 抛出这个错误：

语法错误或访问冲突：1064 您的 SQL 语法有错误；请检查与您的 MySQL 服务器版本相对应的手册，以了解在“rows [...]”附近使用的正确语法。

是因为rows成为了MySQL的保留字吗？

我在无法检查和/或控制的机器上运行查询，所以我不知道原因是否可能是软件更新。

mysql