☰

另一个 INSERT INTO 会覆盖现有数据

tag-icon • tag-icon mysql mac-osx
另一个 INSERT INTO 会覆盖现有数据

为什么 INSERT INTO 会覆盖表中现有的行而不是添加它们？

我有一个简单的查询https://www.db-fiddle.com/f/pAVBB86LCZ8qUe5Jy4ZV6s/1

这是我的导入日志：

191011 17:11:16     21 Query    SET NAMES 'utf8'
            21 Query    # ************************************************************
# Sequel Pro SQL dump
# Version 4541
#
# http://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: xxx (MySQL 5.6.19-67.0-log)
# Database: xxx
# Generation Time: xxx
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
            21 Query    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
            21 Query    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */
            21 Query    /*!40101 SET NAMES utf8 */
            21 Query    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */
            21 Query    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */
            21 Query    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */
            21 Query    # Dump of table options
# ------------------------------------------------------------

DROP TABLE IF EXISTS `options`
            21 Query    /*!40101 SET @saved_cs_client     = @@character_set_client */
            21 Query    /*!40101 SET character_set_client = 'utf8mb4' */
            21 Query    CREATE TABLE `options` (
      `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `option_name` varchar(191) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
      `option_value` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
      `autoload` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'yes',
      PRIMARY KEY (`option_id`),
      UNIQUE KEY `option_name` (`option_name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=69226 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
            21 Query    /*!40101 SET character_set_client = @saved_cs_client */
            21 Query    --
    -- Backup data for table `options`
    --

    LOCK TABLES `options` WRITE
            21 Query    /*!40000 ALTER TABLE `options` DISABLE KEYS */
            21 Query    INSERT INTO `options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES
    (1, 'siteurl', 'https://www.example.de', 'yes')
            21 Query    INSERT INTO `options` (`option_id`, `option_name`, `option_value`, `autoload`) VALUES
    (2, 'home', 'https://www.example.de/home', 'yes')
            21 Query    /*!40000 ALTER TABLE `options` ENABLE KEYS */
            21 Query    UNLOCK TABLES
            21 Query    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
            21 Query    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */
            21 Query    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */
            21 Query    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */
            21 Query    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */
            21 Query    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */
            21 Query    SET NAMES 'utf8mb4'
            21 Query    SET NAMES 'utf8'
            21 Query    SHOW DATABASES
            21 Query    SET NAMES 'utf8mb4'
            21 Query    SELECT DATABASE()
            21 Query    SET NAMES 'utf8'
            21 Query    SHOW /*!50002 FULL*/ TABLES
            21 Query    SELECT * FROM information_schema.routines WHERE routine_schema = 'test' ORDER BY routine_name
            21 Query    SET NAMES 'utf8mb4'
            21 Query    SET NAMES 'utf8'
            21 Query    SHOW TABLE STATUS LIKE 'options'
            21 Query    SHOW CREATE TABLE `options`
            21 Query    SET NAMES 'utf8mb4'
            22 Query    SHOW FULL COLUMNS FROM `options` FROM `test`
            22 Query    SELECT SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE, DEFINER FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'test'
            21 Query    SELECT * FROM `options` LIMIT 0,1000
191011 17:11:17     22 Query    SHOW FULL COLUMNS FROM `options` FROM `test`
            22 Query    SELECT SPECIFIC_NAME, ROUTINE_TYPE, DTD_IDENTIFIER, IS_DETERMINISTIC, SQL_DATA_ACCESS, SECURITY_TYPE, DEFINER FROM `information_schema`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'test'

这里有我的 my.cnf 文件

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

问题 结果，导入后，我创建的表中只有一行 ID 为 2。

| option_id | option_name | option_value                | autoload |
| --------- | ----------- | --------------------------- | -------- |
| 2         | home        | https://www.example.de/home | yes      |

答案1

套接字文件无法连接

错误：无法通过套接字“/tmp/mysql.sock”连接到本地 MySQL 服务器 (2)

这是最终唯一有帮助的事情：https://serverfault.com/a/459403/316405

尝试使用以下方法之一，看看是否可以在没有套接字文件的情况下进行连接

mysql -u root -p -h127.0.0.1

mysql -u root -p -h127.0.0.1 --protocol=tcp

如果你可以连接其中一个，请像这样关闭mysql

mysqladmin -u root -p -h127.0.0.1 --protocol=tcp shutdown

相关内容