MySQL alter table add列具有主键语法错误（语句alter table表名add列定义可以向表中）

25-04-11 4

在这里，我们将给大家分享关于MySQLaltertableadd列具有主键语法错误的知识，让您更了解语句altertable表名add列定义可以向表中的本质，同时也会涉及到如何更有效地ALTERTAB

在这里，我们将给大家分享关于MySQL alter table add列具有主键语法错误的知识，让您更了解语句alter table表名add列定义可以向表中的本质，同时也会涉及到如何更有效地ALTER TABLE ADD COLUMN如果在SQLite中不存在、ALTER TABLE----MySQL 语法、CREATE INDEX vs ALTER TABLE ADD INDEX – MySQLism还是SQL Standard？、My Sql 中要Alter Table的同学请注意！！！_MySQL的内容。

本文目录一览：

MySQL alter table add列具有主键语法错误（语句alter table表名add列定义可以向表中）
ALTER TABLE ADD COLUMN如果在SQLite中不存在
ALTER TABLE----MySQL 语法
CREATE INDEX vs ALTER TABLE ADD INDEX – MySQLism还是SQL Standard？
My Sql 中要Alter Table的同学请注意！！！_MySQL

MySQL alter table add列具有主键语法错误（语句alter table表名add列定义可以向表中）

我正在尝试向我的一个数据库表中添加一列，但是出现语法错误，我似乎找不到问题…

我当前的数据库表如下所示：

component   +  tag_id  +  item_id
------------|----------|-----------
com_content |    23    |    2642
com_content |    26    |    3481
com_content |    35    |    1868
com_content |    85    |    5827
com_content |    89    |    7882

我希望它看起来像这样，其中“ id”是自动递增，并且所有列都是主键的一部分

 id  +  component   +  tag_id  +  item_id
-----|--------------|----------|-----------
  1  |  com_content |    23    |    2642
  2  |  com_content |    26    |    3481
  3  |  com_content |    35    |    1868
  4  |  com_content |    85    |    5827
  5  |  com_content |    89    |    7882

这是我的查询：

DROP PRIMARY KEY
ALTER TABLE gitags_items
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST
PRIMARY KEY (id,component,tag_id,item_id)

但是我收到此错误消息：

#1064 - 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 'PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INC' at line 1

任何帮助/指针将不胜感激

ALTER TABLE ADD COLUMN如果在SQLite中不存在

我们最近需要向一些现有的sqlite数据库表添加列。这可以用 ALTER TABLE ADD COLUMN完成。当然，如果表已经被改变，我们想要离开它。不幸的是，sqlite不支持ALTER TABLE上的IF NOT EXISTS子句。

我们当前的解决方法是执行ALTER TABLE语句并忽略任何“重复列名”错误，就像this Python example(但在C中)。

但是，设置数据库模式的常用方法是使用一个包含CREATE TABLE IF NOT EXISTS和CREATE INDEX IF NOT EXISTS语句的.sql脚本，这些语句可以使用sqlite3_exec或sqlite3命令行工具执行。我们不能把ALTER TABLE放在这些脚本文件中，因为如果该语句失败，它之后的任何东西都不会被执行。

我想要在一个地方的表定义，而不是在.sql和.cpp文件之间拆分。有没有办法写一个解决方法ALTER TABLE ADD COLUMN如果不存在纯sqlite sql？

我有一个99％纯sql方法。想法是版本您的模式。你可以通过两种方式做到这一点：

>使用’user_version’pragma命令(PRAGMA user_version)存储数据库模式版本的增量编号。
>将您的版本号存储在您自己定义的表中。

这样，当软件启动时，它可以检查数据库模式，如果需要，运行您的ALTER TABLE查询，然后增加存储的版本。这是远远好于尝试各种更新“盲”，特别是如果你的数据库增长和多年来改变了几次。

ALTER TABLE----MySQL 语法

ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]alter_specification:    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)        reference_definition
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | FORCE
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONINGindex_col_name:    col_name [(length)] [ASC | DESC]index_type:
    USING {BTREE | HASH}index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT ''string''table_options:    table_option [[,] table_option] ...  (see CREATE TABLE options)partition_options:
    (see CREATE TABLE options)

CREATE INDEX vs ALTER TABLE ADD INDEX – MySQLism还是SQL Standard？

刚遇到一个奇怪的问题,根据我创建索引的方式,需要索引名称.

http://dev.mysql.com/doc/refman/5.5/en/create-index.html

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

CREATE INDEX `random_name` ON `my_table` (`my_column`); # Requires an index name

ALTER TABLE `my_table` ADD INDEX (`my_column`); # Does not require an index name

在我看来,CREATE INDEX调用,不应该使索引名称成为必需.我想知道这是MysqLism还是sql标准？

解决方法

我认为sql标准根本不定义如何创建索引.

来自this Wikipedia page的报价：

Standardization

There is no standard about creating indexes because the ISO sql
Standard does not cover physical aspects. Indexes are one of the
physical parts of database conception among others like storage
(tablespace or filegroups). RDBMS vendors all give a CREATE INDEX
Syntax with some specific options which depends on functionalities
they provide to customers.

The Postgres manual seems to support this here:

There are no provisions for indexes in the sql standard.

More evidence under this related question on SO.

My Sql 中要Alter Table的同学请注意！！！_MySQL

首先我建议你在对MySQL表做DDL操作时：

1 执行 show processlist 查看，要操作的表（数据库对象）是否处于锁状态

1 if("未锁定")

2 {

3 执行DDL语句

4 }else

5 {

6 三思后行

7 }

作为一个程序猿，随着开发的进行，我们要面临需求的变更。

随之而来的有可能就是表结构的变化--字段的增加，字段数据类型的更新。

此时此刻，我就在Alter Table面前跪了。

My Sql 中 Waiting for table metadata lock，主要发生在你在Alter 一个表时，在这个表上有未完成的查询或者操作，其独占了metalock状态下，alter 操作就会等待这个锁释放，接下来是一段漫长的block之旅

以下是mysql 官方对于Metadata Locking概念的解释：

MySQL 5.5.3 and up uses metadata locking to manage concurrent access to database objects and to ensure data consistency. Metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events).

Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex. The following discussion provides some information about how metadata locking works.

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table''s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows:

START TRANSACTION;

SELECT * FROM t;

SELECT * FROM nt;

The server holds metadata locks on both t and nt until the transaction ends. If another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at transaction end. For example, a second session blocks if it attempts any of these operations:

DROP TABLE t;

ALTER TABLE t ...;

DROP TABLE nt;

ALTER TABLE nt ...;

LOCK TABLE t ... WRITE;

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

Before MySQL 5.5.3, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.

此时此刻，我唯一想说的就是，假如我在犯了这个错误之前就知道这个真相是多么幸福的事。。。

我们今天的关于MySQL alter table add列具有主键语法错误和语句alter table表名add列定义可以向表中的分享就到这里，谢谢您的阅读，如果想了解更多关于ALTER TABLE ADD COLUMN如果在SQLite中不存在、ALTER TABLE----MySQL 语法、CREATE INDEX vs ALTER TABLE ADD INDEX – MySQLism还是SQL Standard？、My Sql 中要Alter Table的同学请注意！！！_MySQL的相关信息，可以在本站进行搜索。

本文标签：