ALTER TABLE ORDER BY str_to_date 和 time_to_sec

发布于 2024-10-08 09:40:07 字数 397 浏览 10 评论 0原文

我试图在导入文件后重新排序表，但在此查询中出现 mysql 语法错误：

ALTER TABLE tablename ORDER BY str_to_date(date, '%m/%d/%Y'), time_to_sec(time) ASC

任何人都可以看到此问题吗？这是我收到的错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date, '%m/%d/%Y'), time_to_sec(time) ASC' at line 1

原文

I am trying to re-order a table after importing a file but am getting a mysql syntax error on this query:

ALTER TABLE tablename ORDER BY str_to_date(date, '%m/%d/%Y'), time_to_sec(time) ASC

Can anyone see anything wrong with this? this is the error I am getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(date, '%m/%d/%Y'), time_to_sec(time) ASC' at line 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

岛徒 2024-10-15 09:40:07

根据 mysql 手册，ALTER TABLE ORDER BY 需要一个 col_name，但您尝试使用函数对其进行排序。

我建议创建 2 个新列，用函数结果填充它们，然后按它们排序。

回复收藏 0 原文

寄居人 2024-10-15 09:40:07

您可能必须为此使用临时表，因为您是按函数排序的。

CREATE TABLE temp_tablename AS SELECT * FROM tablename;

TRUNCATE tablename;

INSERT INTO tablename SELECT * FROM temp_tablename;

DROP temp_tablename;

您可以将第一个语句设置为CREATE TEMPORARY TABLE，但是如果您丢失会话，您就会丢失数据。

You might have to use a temp table for this since you're ordering by a function.

CREATE TABLE temp_tablename AS SELECT * FROM tablename;

TRUNCATE tablename;

INSERT INTO tablename SELECT * FROM temp_tablename;

DROP temp_tablename;

You could make the first statement a CREATE TEMPORARY TABLE, but if you lose your session you lose your data.

回复收藏 0 原文