时间戳

首页 > 解决方案 > SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbs1056212.User_Logs' doesn't exist

mysql - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbs1056212.User_Logs' doesn't exist

问题描述

I have SQL file that have the following:

CREATE TABLE `Task_type` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Task_type`
--

INSERT INTO `Task_type` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'Task', NULL, NULL),
(2, 'Meeting', NULL, NULL),
(3, 'Phone call', NULL, NULL),
(4, 'Task', NULL, NULL),
(5, 'Meeting', NULL, NULL),
(6, 'Phone call', NULL, NULL);

--
-- Table structure for table `User_logs`
--

CREATE TABLE `User_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `login` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `logout` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `logged_out` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `User_logs`
--

INSERT INTO `User_logs` (`id`, `user_id`, `login`, `logout`, `time`, `logged_out`) VALUES
(42, 2, '2020-10-14 16:12:37', '2020-10-14 19:09:14', '2020-10-14 16:09:08', 1),
(43, 2, '2020-10-14 20:10:38', '2020-10-15 00:43:04', '2020-10-14 21:43:04', 0),
(44, 1, '2020-10-14 20:46:10', '2020-10-14 20:56:36', '2020-10-14 17:56:17', 1),
(45, 1, '2020-10-14 21:03:09', '2020-10-14 21:06:15', '2020-10-14 18:06:15', 0),
(46, 2, '2020-10-15 07:15:30', '2020-10-15 07:24:38', '2020-10-15 04:19:29', 1),
(47, 2, '2020-10-15 07:24:49', '2020-10-15 07:49:45', '2020-10-15 04:48:48', 1),
(48, 2, '2020-10-15 07:51:25', '2020-10-15 07:57:36', '2020-10-15 04:57:36', 0),
(49, 2, '2020-11-01 21:43:35', '2020-11-01 22:02:26', '2020-11-01 20:02:26', 0);

But, I get the following error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbs1056212.User_Logs' doesn't exist (SQL: insert into User_Logs (user_id, logged_out) values (1, 0))

I have been told that the table names should start with capital letters and the part after the dash is ok to start with lower case letter, and as you see table Task_types naming strategy, create, and insert are exactly the same as User_logs and it is not raising any exception, only table User_logs are raising this issue.

what to do?

database is MySQL

标签： mysqlsql

解决方案

You created the table as User_logs.

The error message says it cannot find User_Logs.

These are not the same, because the case of the "l" is different. Sometimes this makes a difference in MySQL, sometimes it doesn't depending on the value of a system variable lower_case_table_names.

The important consideration is deep in the documentation:

Value 0 . . . Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.

This is the default setting for the system variable lower_case_table_names on Unix systems (but not on MacOs which is Unix-like). You can override it when creating the database, but that is rather unusual.

推荐阅读