我有一个具有这样价值的专栏。
Declare @Table TABLE (ID int, Val varchar(100))
Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'
我想动态提取 Jan 作为月份,2024 年作为年份。
我有一个具有这样价值的专栏。
Declare @Table TABLE (ID int, Val varchar(100))
Insert into @Table
Select 1, 'happy_Summer_Holiday_by_Jan_2024.xlsx'
我想动态提取 Jan 作为月份,2024 年作为年份。
目前,我正在使用以下代码片段逐列索引:
DECLARE
already_exists EXCEPTION;
columns_indexed EXCEPTION;
PRAGMA EXCEPTION_INIT ( already_exists, -955 );
PRAGMA EXCEPTION_INIT (columns_indexed, -1408);
BEGIN
EXECUTE IMMEDIATE 'Create Index TABLE_A_COLUMN_A on TABLE_A(COLUMN_A)';
EXCEPTION
WHEN already_exists or columns_indexed
THEN
NULL;
END;
COLUMN_A
这段代码可以替换吗
a) 使用列表引用并动态迭代它?
declare @myList varchar(100)
set @myList = 'COLUMN_A,COLUMN_B,COLUMN_C'
b) 并确保循环即使遇到一列问题也能保持循环?换句话说,如果其中一列出现问题,则整个循环不应失败
某些 SQL Server 代理作业最近因死锁错误而失败。我想密切关注这个问题,并在 SSMS 中创建了监控警报。然而,该事件从未触发......这似乎是错误的。
警报脚本:
EXEC msdb.dbo.sp_add_alert @name=N'Deadlock(s) detected . . .',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=720,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'Locks|Number of Deadlocks/sec|Database|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Deadlock(s) detected . . .',
@operator_name=N'Watchdog', @notification_method = 1
少了什么东西?或者有更好的方法来做到这一点吗?
我已经得到了如图所示的复制配置。一切都会复制到任何地方。其中一张表维护会话数据,其中包含由于过期而必须删除的行。我创建了一个事件,每天检查一次过期会话并删除它们:
DELETE FROM session
WHERE expiration < (UNIX_TIMESTAMP() - 7200);
我的问题是:
请注意,站点 A 和站点 B 在物理上是分开的(美国东海岸和西海岸),因此存在网络延迟。
编辑:这是 MySQL 8.0.36
请给我一些建议吗?我们遇到过 TEMPDB 日志已满的情况,我们无法从 SSMS 连接到 SQL 2019 AG 的主副本。但是,我们能够连接到辅助副本,并在其中启动从主副本到辅助副本的故障转移。故障转移回滚了主 tempdb 上正在进行的事务,因此无论导致日志被填满的事务是什么,都会回滚并释放日志。然后,我们能够连接到该副本并将其作为主副本进行故障恢复。
我的问题是:在每个服务器节点上重新启动 SQL 服务是否有利于避免临时日志再次填满 - 即重新创建干净的空临时数据库?
我有一个表存储一些事件的开始时间和结束时间。我想在这个表中搜索所有事件,甚至是在特定日期发生的事情。
这是示例数据:
ID | 开始日期 | 结束日期 | 姓名 |
---|---|---|---|
1 | 2024-04-18 13:30:45 | 2024-04-18 15:30:45 | 18-04 |
2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 |
3 | 2024-04-15 13:30:45 | 2024-04-15 15:30:45 | 15-04 |
4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 |
5 | 2024-04-12 13:30:45 | 2024-04-12 15:30:45 | 12-04 |
6 | 2024-03-18 13:30:45 | 2024-03-18 15:30:45 | 18-03 |
7 | 2024-03-15 13:30:45 | 2024-03-16 15:30:45 | 15-03 |
8 | 2024-03-14 13:30:45 | 2024-03-15 15:30:45 | 14-03 |
9 | 2024-03-12 13:30:45 | 2024-03-13 15:30:45 | 12-03 |
如果我想要 14/04 的数据,这是例外的输出:
ID | 开始日期 | 结束日期 | 姓名 |
---|---|---|---|
2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 |
4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 |
这是我的查询:
SELECT * FROM content WHERE
DATE_FORMAT(dateBegin, '%d/%m/%Y') <= '14/04/2024'
AND
DATE_FORMAT(dateEnd, '%d/%m/%Y') >= '14/04/2024'
但这是我的实际输出:
ID | 开始日期 | 结束日期 | 姓名 |
---|---|---|---|
2 | 2024-04-14 13:30:45 | 2024-04-18 15:30:45 | 18-04 |
4 | 2024-04-14 13:30:45 | 2024-04-14 15:30:45 | 14-04 |
8 | 2024-03-14 13:30:45 | 2024-03-15 15:30:45 | 14-03 |
为什么我收到的线路是正确的一天,但却是另一个月的?
我做了一个Db Fiddle来展示这个问题。
我该如何修复它?
注意:我使用的是 MariaDB 10.11.4。
我已经准备好拔掉我的头发了。我制作了一个脚本来备份和恢复构建查询的数据库和文件组,例如:
RESTORE DATABASE [dashdb_dev01_2024-04-11 143711] FROM DISK = 'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak' WITH REPLACE, RECOVERY, MOVE [dashdb_dev01_2024-04-11 143711] TO 'E:\SQL2019_Data\Backup\OLD\dashdb_dev01_2024-04-11 143711.mdf'
我得到了:
Msg 102, Level 15, State 1, Server FRPARPLMA-DBDEV, Line 1 Incorrect syntax near 'dashdb_dev01_2024-04-11 143711'.
我用来[...]
转义数据库名称,但我从 MSG 102 中了解到这是第 102 个字符(我就在这里?)。所以这是'E:\SQL2019_Data\Backup\dev01\20240410\1437\dashdb_dev01_2024-04-11 143711.bak'
之后的FROM DISK
。
我想,我该如何逃离这里的空间呢?
pg_log_backend_memory_contexts
(PG14.1) 下面是PID的输出,其中pg_proctab()
表示。我添加了一些点,以便更轻松地读取大量数字,并且匿名了一些内容。rss
216MB
我不明白:
logging memory contexts of PID 15744
level: 0; TopMemoryContext: 4.264.856 total in 104 blocks; 2.074.448 free (8510 chunks); 2190408 used
level: 1; pgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 4672 free (11 chunks); 60864 used
level: 1; RdsSuperUserCache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3792 free (1 chunks); 4400 used: get_date_to_char(timestamp without time zone,character varying)
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 7032 free (5 chunks); 9352 used: get_number_to_char(numeric,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3824 free (3 chunks); 4368 used: get_to_number(character varying,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4504 free (1 chunks); 3688 used: <FUNCTION>
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 6616 free (4 chunks); 9768 used: <FUNCTION2>
level: 1; Sequence values: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; Btree proof lookup cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5752 free (5 chunks); 10632 used: get_date_interval(character varying,numeric)
level: 1; PL/pgSQL function: 32768 total in 3 blocks; 16608 free (5 chunks); 16160 used: <FUNCTION3>
level: 1; Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
level: 1; RI compare cache: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; RI query cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; RI constraint cache: 40648 total in 2 blocks; 2616 free (0 chunks); 38032 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4592 free (1 chunks); 3600 used: get_systimestamp()
level: 1; Type information cache: 24376 total in 2 blocks; 2616 free (0 chunks); 21760 used
level: 1; PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PLpgSQL cast expressions: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5992 free (4 chunks); 10392 used: <FUNCTION4>
level: 1; Function stat entries: 16384 total in 2 blocks; 6640 free (2 chunks); 9744 used
level: 1; CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
level: 1; Prepared Queries: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
level: 1; RowDescriptionContext: 57400 total in 3 blocks; 24272 free (7 chunks); 33128 used
level: 1; MessageContext: 8192 total in 1 blocks; 6888 free (1 chunks); 1304 used
level: 1; Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; smgr relation table: 2.097.152 total in 9 blocks; 269224 free (33 chunks); 1827928 used
level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
level: 1; Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; TopPortalContext: 8192 total in 1 blocks; 7928 free (2 chunks); 264 used
level: 1; Relcache by OID: 1.048.576 total in 8 blocks; 500672 free (16 chunks); 547904 used
level: 1; CacheMemoryContext: 69.478.096 total in 208 blocks; 44.098.912 free (106056 chunks); 25.379.184 used
level: 2; CachedPlanSource: 4096 total in 3 blocks; 1960 free (1 chunks); 2136 used: <INSERT>
level: 3; unnamed prepared statement: 16384 total in 2 blocks; 6824 free (1 chunks); 9560 used
level: 2; CachedPlan: 8.420.408 total in 15 blocks; 1409824 free (1 chunks); 7010584 used: <SELECT>
level: 2; index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: pg_toast_295195_index
level: 2; CachedPlan: 8192 total in 4 blocks; 2480 free (0 chunks); 5712 used: <DELETE>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_sequence_seqrelid_index
level: 2; CachedPlan: 2048 total in 2 blocks; 280 free (0 chunks); 1768 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 16 free (0 chunks); 2032 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 160 free (1 chunks); 1888 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; index info: 3072 total in 2 blocks; 696 free (1 chunks); 2376 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedExpression: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
level: 2; CachedPlan: 2048 total in 2 blocks; 208 free (1 chunks); 1840 used: TO_NUMBER(p_input, p_fmt)
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 920 free (0 chunks); 1128 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3544 total in 3 blocks; 960 free (1 chunks); 2584 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4096 total in 3 blocks; 1912 free (3 chunks); 2184 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 840 free (0 chunks); 1208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 504 free (0 chunks); 1544 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4024 total in 3 blocks; 816 free (1 chunks); 3208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4744 total in 3 blocks; 240 free (0 chunks); 4504 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 8192 total in 4 blocks; 3824 free (2 chunks); 4368 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: v_interval::INTERVAL
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: (v_interval IS NOT NULL)
level: 2; CachedPlan: 2048 total in 2 blocks; 144 free (1 chunks); 1904 used: v_interval := p_interval_length || ' second'
level: 2; CachedPlan: 2048 total in 2 blocks; 224 free (1 chunks); 1824 used: (p_interval = 'SECOND')
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 1; 4141 more child contexts containing 12.748.648 total in 8713 blocks; 3.899.520 free (3559 chunks); 8.849.128 used
level: 1; WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
level: 1; PrivateRefCount: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; MdSmgr: 524.288 total in 7 blocks; 412.640 free (8661 chunks); 111648 used
level: 1; LOCALLOCK hash: 1.048.576 total in 8 blocks; 215936 free (27 chunks); 832640 used
level: 1; Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
Grand total: 100.635.144 bytes in 9342 blocks; 53.175.720 free (127010 chunks); 47459424 used
今天,我猜一个月左右后,我重新启动了服务器,现在我得到了
Table 'ANY TABLE' doesn't exist in engine
在我想在 phpMyAdmin 中打开的每个 InnoDB 表中。我的 ISAM 表运行良好。我看到其他人在将文件从旧备份移动到 MySQL 目录后遇到了同样的问题,但我没有这样做。
另一个奇怪的事情是我有一个来自一台机器(VMware)的备份,但是当我从这些机器中恢复时(我尝试了 2 天前的备份和 1 周前的备份),我得到了同样的错误,但数据库工作正常直到今天。
所以我没有 SQL 备份,只有 VMware 机器备份,但即使是 2 周前的完整机器备份也无法解决我的问题。我猜这是因为我很久以前做了一个更改,但直到服务器重置才显示出来。我可以做些什么来恢复我的数据库并取回我的数据吗?
我在 Linux 上使用 XAMPP。运行MariaDB没有报错。InnoDB 强制恢复甚至没有帮助 6。我将所有数据文件夹 + ib_logfiles + ibdata1 移动到新的 XAMPP 上,但出现完全相同的错误。
我发现了日期的整数格式,我也知道日期,但我不知道如何在 TSQL 中获取该日期,而且如果我有日期,我也不知道如何获取整数:
700444 -> 1918-10-02
731573 -> 2003-12-24
739479 -> 2025-08-16
这些 6 位数字适合作为从 0001-01-01 开始的每一天的计数器,我通过获取从接近 2000 年的日期开始一个世纪的天数并将其添加到 1900 来进行检查:
select DATEADD(dd,731573/20,'19000101')
出去:
2000-02-24 00:00:00.000
但我无法运行select DATEADD(dd,731573/20,'10000101')
,这会抛出:
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。
Microsoft Learn 表示 TSQL 仅允许从 1753-01-01 开始的日期,请参阅datetime (Transact-SQL) Microsoft Learn,因此:
select DATEADD(dd,731573/20,'17530101')
出去:
1853-02-24 00:00:00.000
不过,我无法将 731573 添加到第一年。然后我发现1/1/1753在SQL Server中有何意义?:
--(正如其中一个答案和为什么你应该总是写“varchar”,其长度放在括号中?通常,如果不这样做,您会得到正确的结果 - DBA SE,varchar(length)
而不是仅仅varchar
)--
选择 CONVERT(VARCHAR, DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)
SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)
出去:
Jan 1 0001 12:00AM
为了证明这一点,数字是从 0001 年第一天开始的天数。现在我想知道是否可以在不将日期时间列格式化为 datetime2 的情况下到达那里。我的日期都在 20 世纪和 21 世纪,所以我不需要datetime2
. 我以日期时间形式获取数据并尝试避免类型转换。
如何将这个七千整数作为从 1 年到日期的天数计数器,以及如何在不将日期转换为 datetime2 的情况下从日期返回到该整数?