精华内容
下载资源
问答
  • ERROR 1062 (23000): Duplicate entry '154878957' for key 'PRIMARY'对一个大表 进行online DDL的时候偶尔回出现一下报错 ,在mysql 官方查找到 相关bug idhttps://bugs.mysql.com/bug.php?id=76...



    alter table tt  ENGINE=InnoDB ; 

    ERROR 1062 (23000): Duplicate entry '154878957' for key 'PRIMARY'


    对一个大表 进行online DDL的时候偶尔回出现一下报错 , 

    在mysql 官方查找到 相关bug id  https://bugs.mysql.com/bug.php?id=76895

    但是这个bug并没有被确认 ,也就说严格意义上不算一个bug 。

    查看官方文档 online DDL 部分资料 


    image.png




    官方文档资料

    https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html




    转载于:https://blog.51cto.com/dwchaoyue/2313356

    展开全文
  • 有一个项目,数据库使用mysql mariadb 10.1.11版本,系统开发是交给外包公司,发现在测试和验证环境所有表都是用varchar型的非自增字段作为主键,如下所示:  Table: w_t_p Create Table: CREATE TABLE `w_t_p` ...

    有一个项目,数据库使用mysql mariadb 10.1.11版本,系统开发是交给外包公司,发现在测试和验证环境所有表都是用varchar型的非自增字段作为主键,如下所示:
          Table: w_t_p
    Create Table: CREATE TABLE `w_t_p` (
      `id` varchar(40) NOT NULL COMMENT '主键',
    +--------------------------------------+
    | id                                   |
    +--------------------------------------+
    | 3f573f41-3c5c-4e0b-94f4-20785dc6e402 |
    | 4f78eb50-b476-43d9-a4da-50f09ecbe057 |
    | 6425942e-27ba-4ae6-abda-79e632c75c25 |
    +--------------------------------------+

    如果不用自增数字键作为主键,会有什么问题呢?
    问题很大,主要有如下:
    1. 表容易产生碎片,由于碎片的存在,表会变得很大,严重影响查询和DML操作性能
    2. 由于主键不是自增的,数据将不会按顺序插入,需要查找合适位置随机插入,严重影响数据插入性能。
    3. 主键用uuid值要比用自增数字值所占空间大,同样二级索引也变大,使得用主键或二级索引来查询数据效率相对变低。
    强建要求开发人员将主键改为自增数字主键,但要怎么去修改比较好呢?作为DBA,这时需要给出一个比较好的简单的方案给开发人员参考。
    于是,DBA动手实验如下:(网上查找各种信息,难以见到有类似方法)
    建立相同数据的表:
    dba@192.168.1.20;create table g_o_test as select * from g_o;
    ERROR 1173 (42000): This table type requires a primary key
    关闭主键强制特性:
    set global  innodb_force_primary_key=off;
    create table g_o_test as select * from g_o;
    删除_id字段和修改id字段为为主键:
    alter table g_o_test drop column `_id`,modify column `id` varchar(50) NOT NULL COMMENT '主键:系统自动生成' primary key;
    show create table g_o_test\G
         Table: g_o_test
    Create Table: CREATE TABLE `g_o_test` (
      `id` varchar(50) NOT NULL COMMENT '主键:系统自动生成',
    ............................
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    开启主键强制特性:
    set global  innodb_force_primary_key=on;
    show global variables like 'innodb_force_primary_key';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_force_primary_key | ON    |
    dba@10.16.74.60:3306(db(master)) : midea_gls_ver 09:17:49>alter table g_o_test drop primary key,add unique key (`id`);
    Query OK, 0 rows affected (42.33 sec)               
    Records: 0  Duplicates: 0  Warnings: 0
    dba@10.16.74.60:3306(db(master)) : midea_gls_ver 09:29:10>alter table g_o_test add column `_id` int not null auto_increment primary key comment '主键' first;
    Query OK, 0 rows affected (29.15 sec)               
    Records: 0  Duplicates: 0  Warnings: 0
    这样也没有报错,看起来在开启innodb_force_primary_key的情况下,上面操作修改主键也是可以的,但最好方法是:
    一个ddl将drop主键、增加唯一键、修改字段、增加主键四个操作合在一起完成:
    alter table g_o_test drop primary key,add unique key (`id`),modify column `id` varchar(50) NOT NULL COMMENT '',add column `_id` int not null auto_increment primary key comment '主键' first;
    show create table g_o_test\G
    *************************** 1. row ***************************
           Table: g_o_test
    Create Table: CREATE TABLE `g_o_test` (
      `_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `id` varchar(50) NOT NULL COMMENT '',
    ......................................
      PRIMARY KEY (`_id`),
      UNIQUE KEY `id` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=423585 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)


     


     

    展开全文
  • --获取建表DDL(+主键) declare @tabname varchar(50) set @tabname='testbzm'--表名if ( object_id('tempdb.dbo.#t') is not null) begin DROP TABLE #t endselect 'create table [' + so.name + '] (' + o.list + '...
    --获取建表DDL(+主键)
    declare @tabname varchar(50)
    set @tabname='testbzm'--表名
    
    if ( object_id('tempdb.dbo.#t') is not null)
    begin
    DROP TABLE #t
    end
    
    select  'create table [' + so.name + '] (' + o.list + ')' 
        + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END 
        TABLE_DDL
    into #t from    sysobjects so
    cross apply
        (SELECT 
            '  ['+column_name+'] ' + 
            data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'ntext' then ''
                when 'xml' then ''
                when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
            case when exists ( 
            select id from syscolumns
            where object_name(id)=so.name
            and name=column_name
            and columnproperty(id,name,'IsIdentity') = 1 
            ) then
            'IDENTITY(' + 
            cast(ident_seed(so.name) as varchar) + ',' + 
            cast(ident_incr(so.name) as varchar) + ')'
            else ''
            end + ' ' +
             (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
              case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 
    
         from information_schema.columns where table_name = so.name
         order by ordinal_position
        FOR XML PATH('')) o (list)
    left join
        information_schema.table_constraints tc
    on  tc.Table_name       = so.Name
    AND tc.Constraint_Type  = 'PRIMARY KEY'
    cross apply
        (select '[' + Column_Name + '], '
         FROM   information_schema.key_column_usage kcu
         WHERE  kcu.Constraint_Name = tc.Constraint_Name
         ORDER BY
            ORDINAL_POSITION
         FOR XML PATH('')) j (list)
    where   xtype = 'U'
    AND name=@tabname
    
    select 'USE '+db_name() +CHAR(13) +'GO' + CHAR(13) +
    (--区别有主键和没主键
    case when (select count(a.constraint_type)
    from information_schema.table_constraints a 
    inner join information_schema.constraint_column_usage b
    on a.constraint_name = b.constraint_name
    where a.constraint_type = 'PRIMARY KEY'--主键 
    and a.table_name = @tabname)=1 then
    replace(table_ddl,', )ALTER TABLE',')'+CHAR(13)+'ALTER TABLE')
    else SUBSTRING(table_ddl,1,len(table_ddl)-3)+')' end
    ) from #t
    
    --drop table #t
    
    展开全文
  • 导读:本文主要详细测试online DDL中的删除,添加主键操作。关于MySQL5.6在线DDL的全文信息,请参照:MySQL5.6版本InnoDB存储引擎在线DDL变更的官方信息中文翻译版,文章地址:...

    导读:本文主要详细测试online DDL中的删除,添加主键操作。关于MySQL5.6在线DDL的全文信息,请参照:MySQL5.6版本InnoDB存储引擎在线DDL变更的官方信息中文翻译版,
    文章地址:http://www.mysqlops.com/2013/03/26/mysql56-innodb-ddl.html

    测试目的主要有以下几点:
    (1):以alter table的方式新增主键,MySQL需要做什么
    (2):ALGORITHM=inplace与ALGORITHM=copy 增删主键的不同之处,和适用于怎么的环境
    (3):inplace与copy对于重建主键的效率,考虑哪些元素
    (4):online DDL是如何控制DML,查询的并发

    1:测试online DDL新增主键操
    mysql> show variables like ‘%sql_mode%’;
    +—————+————————+
    | Variable_name | Value |
    +—————+————————+
    | sql_mode | NO_ENGINE_SUBSTITUTION |
    +—————+————————+
    1 row in set (0.00 sec)

    用例1:
    mysql> CREATE TABLE `tpri` (  `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)
    mysql>  insert into tpri values(null,1);
    Query OK, 1 row affected (0.01 sec)
    mysql> alter table tpriadd primary key(b),algorithm=inplace;
    ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
    Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.

    //错误提示:不支持ALGORITHM=INPLACE。因为在这种SQL_MODE 模式下,不能静态转换NULL值,尝试ALGORITHM=COPY

    用例2:
    mysql> CREATE TABLE `tpri2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)
    mysql> insert into tpri2 values(null,1);
    Query OK, 1 row affected (0.01 sec)
    mysql> alter table tpri2 add primary key(b),algorithm=copy;
    Query OK, 2 rows affected, 1 warning (0.20 sec)
    Records: 2 Duplicates: 0 Warnings: 1
    mysql> show create table tpri2 \G
    *************************** 1. row ***************************
    Table: tpri2
    Create Table: CREATE TABLE `tpri2` (
    `a` int(11) DEFAULT NULL,
    `b` int(11) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.02 sec)

    用例3:
    mysql> CREATE TABLE `tpri3` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)
    mysql> show create table tpri3 \G
    *************************** 1. row ***************************
    Table: tpri3
    Create Table: CREATE TABLE `tpri3` (
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    mysql> insert into tpri3 values(null,1);
    Query OK, 1 row affected (0.02 sec)
    mysql> alter table tpri3 add primary key(b);
    Query OK, 1 row affected (0.19 sec)
    Records: 1 Duplicates: 0 Warnings: 0
    mysql> show create table tpri3 \G
    *************************** 1. row ***************************
    Table: tpri3
    Create Table: CREATE TABLE `tpri` (
    `a` int(11) DEFAULT NULL,
    `b` int(11) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.01 sec)

    用例4:
    mysql> set sql_mode=’strict_trans_tables’;
    Query OK, 0 rows affected (0.00 sec)
    mysql> CREATE TABLE `tpri4` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)
    mysql> alter table tpri4 add primary key(b),algorithm=inplace;
    Query OK, 0 rows affected (0.12 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> show create table tpri4 \G
    *************************** 1. row ***************************
    Table: tpri4
    Create Table: CREATE TABLE `tpri4` (
    `a` int(11) DEFAULT NULL,
    `b` int(11) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    用例5:
    mysql> CREATE TABLE `tpri5` ( `a` int(11) DEFAULT NULL, `b` int(11) default NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.04 sec)
    mysql> insert into tpri5 values(null,1);
    Query OK, 1 row affected (0.01 sec)
    mysql> alter table tpri5 add primary key(b);
    Query OK, 0 rows affected (0.08 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    用例6:
    mysql> CREATE TABLE `tpri6` ( `a` int(11) DEFAULT NULL, `b` int(11) default NULL ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.05 sec)
    mysql> insert into tpri5 values (null,1),(1,null),(2,null);
    Query OK, 3 rows affected (0.01 sec)
    Records: 3 Duplicates: 0 Warnings: 0
    mysql> alter table tpri5 add primary key(b),ALGORITHM=COPY;
    ERROR 1265 (01000): Data truncated for column ‘b’ at row 2
    mysql> alter table tpri5 add primary key(b),ALGORITHM=INPLACE;
    ERROR 1138 (22004): Invalid use of NULL value

    小结1:
    当重建主键时,MySQL需要做额外的工作,检查表中的主键列是否有重复值,和null值,并且检查列定义时是否允许为NULL;

    如上面测试用例中的b default null 在添加主键过程中,转换为 not null default 0(数值型默认为0,字符和BLOB 默认为空字符).

    从测试用例1,2,4中可以发现:对于主键列为null时,alter table …. add primary key 只在sql_mode 包含srict_trans_table
    或strict_all_tables标志下才支持ALGORITHM=INPLACE,否则,强制指定ALGORITHM=INPLACE,会出现测试用例1的错误信息。

    从测试用例3,5可知,没有指定ALGORITHM子句时,MySQL会自动选择允许的算法(如果都支持,则ALGORITHM=INPLACE优先),
    测试用例6表明,主键列有NULL值,不管以什么算法去重建主键,都是会失败的。

    2:alter table …. add primary key 下的ALGORITHM=INPLACE一定比ALGORITHM=copy快吗?
    mysql> show create table big_table \G
    *************************** 1. row ***************************
    Table: big_table
    Create Table: CREATE TABLE `big_table` (
    `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT ”,
    `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT ”,
    `TABLE_NAME` varchar(64) NOT NULL DEFAULT ”,
    `COLUMN_NAME` varchar(64) NOT NULL DEFAULT ”,
    `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT ’0′,
    `COLUMN_DEFAULT` longtext,
    `IS_NULLABLE` varchar(3) NOT NULL DEFAULT ”,
    `DATA_TYPE` varchar(64) NOT NULL DEFAULT ”,
    `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
    `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
    `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
    `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
    `DATETIME_PRECISION` bigint(21) unsigned DEFAULT NULL,
    `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
    `COLLATION_NAME` varchar(32) DEFAULT NULL,
    `COLUMN_TYPE` longtext NOT NULL,
    `COLUMN_KEY` varchar(3) NOT NULL DEFAULT ”,
    `EXTRA` varchar(30) DEFAULT NULL,
    `PRIVILEGES` varchar(80) NOT NULL DEFAULT ”,
    `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ”,
    `id` int(11) NOT NULL DEFAULT ’0′,
    `id2` int(11) NOT NULL DEFAULT ’0′,
    PRIMARY KEY (`id2`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 KEY_BLOCK_SIZE=8
    1 row in set (0.03 sec)
    mysql> select count(*) from big_table ;
    +———-+
    | count(*) |
    +———-+
    | 1750016 |
    +———-+
    1 row in set (4.90 sec)

    第一组测试
    1:mysql> alter table big_table add column id int not null auto_increment primary key,ALGORITHM=copy;
    Query OK, 1750016 rows affected, 2 warnings (48.99 sec)
    Records: 1750016 Duplicates: 0 Warnings: 2

    2:mysql> alter table big_table add column id int not null auto_increment primary key,ALGORITHM=INPLACE;
    Query OK, 0 rows affected, 2 warnings (2 min 16.79 sec)
    Records: 0 Duplicates: 0 Warnings: 2

    3:mysql> alter table big_table add primary key(id),ALGORITHM=INPLACE;
    Query OK, 0 rows affected, 2 warnings (2 min 12.44 sec)
    Records: 0 Duplicates: 0 Warnings: 2

    4:mysql> alter table big_table add primary key(id),ALGORITHM=copy;
    Query OK, 1750016 rows affected, 2 warnings (47.69 sec)
    Records: 1750016 Duplicates: 0 Warnings: 2

    5:mysql> alter table big_table drop primary key ,add primary key(id2),ALGORITHM=copy;
    Query OK, 1750016 rows affected, 2 warnings (48.51 sec)
    Records: 1750016 Duplicates: 0 Warnings: 2

    6:mysql> alter table big_table drop primary key ,add primary key(id),ALGORITHM=inplace;
    Query OK, 0 rows affected, 2 warnings (2 min 19.99 sec)
    Records: 0 Duplicates: 0 Warnings: 2

    第二组测试
    mysql> alter table big_table modify id int(11) not null default 0;
    Query OK, 1750016 rows affected, 2 warnings (46.60 sec)
    Records: 1750016 Duplicates: 0 Warnings: 2

    7:mysql> alter table big_table drop primary key,ALGORITHM=INPLACE;
    ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY.
    8:mysql> alter table big_table drop primary key,ALGORITHM=copy;
    Query OK, 1750016 rows affected, 2 warnings (50.19 sec)
    Records: 1750016 Duplicates: 0 Warnings: 2

    <strong> 小结2:</strong>

    从第一组测试中的alter table….add …primary key 的各种形式表明:选用ALGORITHM=INPLACE的执行效率明显比ALGORITHM=copy的效率慢
    (大家如果做实验时一定要注意数据量的多少,数据量小的话则测试结果可能会刚好相反的)。是什么原因呢?我认为可以从数据存储来分析,
    InnoDB的数据存储以主键聚集来组织的。

    采用algorithm=inplace新建主键,虽然避免表的复制,但数据需要重新进行重组的。ALGORITHM=copy把最初表拷贝
    到临时表,然后把临时表的数据插入到新表。具体情况需要分析这两种算法的源代码。 对于alter table … drop primary key 之后没有添加新的主键,
    那么只能采用ALGORITHM=copy,如果强制采用ALGORITHM=inplace会出现7中错误。

    建议:
    (1):在创建表时,指定主键,避免使用alter table 创建主键重建表
    (2):对大数据重建主键建议采用 ALGORITHM=copy,数据量小可以采用默认情况。

    3:并发测试
    3.1:默认锁级别下的
    session 1 DML session2 SELECT session3 DDL
    set autocommit=0 set autocommit=0
    update big_table set TABLE_SCHEMA=’test2′ select count(*) from big_table where alter table big_table drop primary key ,
    where TABLE_SCHEMA=’test’ TABLE_SCHEMA=’test2′ add primary key(id2)
    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+—————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+—————————————————+
    | 16 | root | localhost | test | Query | 3 | Waiting for table metadata lock | alter table big_table drop primary key ,add primary key(id2)|
    | 17 | root | localhost | test | Query | 8 | updating | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 5 | Sending data | select count(*) from big_table where TABLE_SCHEMA=’test2′|
    +—-+——+———-+——+———+——+———————————+—————————————————+
    Query OK, 34816 rows affected (14.96 sec) 1 row in set (10.18 sec) waiting
    Rows matched: 34816 Changed: 34816 Warnings: 0
    mysql&gt;commit; waiting

    mysql&gt;commit; altering

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+—————-+————————————————————-+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+—————-+————————————————————-+
    | 16 | root | localhost | test | Query | 55 | altering table | alter table big_table drop primary key ,add primary key(id2) |
    | 17 | root | localhost | test | Sleep | 23 | | NULL |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 4 | | NULL |
    +—-+——+———-+——+———+——+—————-+————————————————————-+

    uery OK, 0 rows affected, 2 warnings (3 min 22.00 sec)
    小结3:
    从线程列表中,可以知道事务发起顺序是session1发起DML操作,session2发起查询,session3最后发起online DDL,
    DDL一直等待在DDL发起之前的其他的事务的提交和回滚,然后才执行DDL操作。在这种情况下,online DDL操作是允许进行DML,查询并发。
    有一个疑惑:在DDL之后(即在DDL进行时)发起的事务,是否允许?见如下测试(session1,session2,session3 执行与上面相同的操作)。

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+—————-+———————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+—————-+———————————————————————+
    | 16 | root | localhost | test | Query | 16 | altering table | alter table big_table drop primary key ,add primary key(id2) |
    | 17 | root | localhost | test | Query | 12 | updating | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 9 | Sending data | select count(*) from big_table where TABLE_SCHEMA=’test’ |
    +—-+——+———-+——+———+——+—————-+———————————————————————+
    4 rows in set (0.00 sec)

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+—————-+————————————————————-+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+—————-+————————————————————-+
    | 16 | root | localhost | test | Query | 22 | altering table | alter table big_table drop primary key ,add primary key(id) |
    | 17 | root | localhost | test | Sleep | 20 | | NULL |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 21 | | NULL |
    +—-+——+———-+——+———+——+—————-+————————————————————-+
    4 rows in set (0.00 sec)

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | 16 | root | localhost | test | Query | 208 | Waiting for table metadata lock | alter table big_table drop primary key ,add primary key(id) |
    | 17 | root | localhost | test | Sleep | 203 | | NULL |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 205 | | NULL |
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    4 rows in set (0.00 sec)
    commit; commit;
    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+——-+——————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+——-+——————+
    | 16 | root | localhost | test | Sleep | 277 | | NULL |
    | 17 | root | localhost | test | Sleep | 26 | | NULL |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 18 | | NULL |
    +—-+——+———-+——+———+——+——-+——————+

    小结4:
    已经可以回答上面的疑惑了,在DDL发起和完成之前,允许在这期间DML,查询(注意这些事务都没有明确指定锁级别)的并发。但DDL在完成时,
    还是需要获取表的元数据锁。因为online DDL在 preparing for alter table,committing alter table to stor这两个阶段需要获取表的排斥锁
    (这期间是短暂),在altering table 阶段允许其他事务并发的。(online DDL 的操作步骤可以通过打开profiling,来查看,这里就不列出来)。

    为了方便让大家更好发现online DDL和以前机制(ALGORITHM=copy)的DDL的区别,见下面的测试:

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | 16 | root | localhost | test | Query | 10 | copy to tmp table | alter table big_table drop primary key ,add primary key(id2),ALGORITHM=copy |
    | 17 | root | localhost | test | Query | 7 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 5 | Sending data | select count(*) from big_table where TABLE_SCHEMA=’test’ |
    4 rows in set (0.00 sec)
    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+————————————————————-+
    | 16 | root | localhost | test | Query | 16| copy to tmp table | alter table big_table drop primary key ,add primary key(id2),ALGORITHM=copy |
    | 17 | root | localhost | test | Query | 13 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 11 | Sending data | select count(*) from big_table where TABLE_SCHEMA=’test’ |
    4 rows in set (0.00 sec)

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    | 16 | root | localhost | test | Query | 54 | rename result table | alter table big_table drop primary key ,add primary key(id2),ALGORITHM=copy |
    | 17 | root | localhost | test | Query | 51 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 49 | | NULL |
    +—-+——+———-+——+———+——+———————————+—————————————————————-+
    4 rows in set (0.00 sec)

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———-+———————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———-+———————————————————————+
    | 16 | root | localhost | test | Sleep | 60 | | NULL |
    | 17 | root | localhost | test | Query | 57 | updating | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 55 | | NULL |
    +—-+——+———-+——+———+——+———-+———————————————————————+
    4 rows in set (0.00 sec)

    小结5:ALGORITHM=copy的机制,是不允许DML并发的,但允许查询。

    <strong> 3.2:lock=shared下的并发</strong>
    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+————————————————————- +
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    | 16 | root | localhost | test | Query | 8 | altering table | alter table big_table drop primary key ,add primary key(id2),lock=shared |
    | 17 | root | localhost | test | Query | 3 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 6 | Sending data | select count(*) from big_table where TABLE_SCHEMA=’test2′ |
    +—-+——+———-+——+———+——+———————————+—————————————— ————+
    4 rows in set (0.00 sec)

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+————————————————- ————-+
    | 16 | root | localhost | test | Query | 58 | altering table | alter table big_table drop primary key ,add primary key(id2),lock=shared |
    | 17 | root | localhost | test | Query | 53 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 56 | | NULL |
    +—-+——+———-+——+———+——+———————————+——————————————— ————-+

    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———-+———————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———-+———————————————————————+
    | 16 | root | localhost | test | Sleep | 160 | | NULL |
    | 17 | root | localhost | test | Query | 155 | updating | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Sleep | 158 | | NULL |
    +—-+——+———-+——+———+——+———-+———————————————————————+
    4 rows in set (0.00 sec)

    小结6:lock=shared,不允许DML并发,但允许查询执行。

    3.3:lock=exclusive
    mysql&gt; show processlist;
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+——+———-+——+———+——+———————————+———————————- —————————+
    | 16 | root | localhost | test | Query | 11 | altering table | alter table big_table drop primary key ,add primary key(id2),lock=exclusive |
    | 17 | root | localhost | test | Query | 8 | Waiting for table metadata lock | update big_table set TABLE_SCHEMA=’test2′ where TABLE_SCHEMA=’test’ |
    | 18 | root | localhost | NULL | Query | 0 | init | show processlist |
    | 19 | root | localhost | test | Query | 9 | Waiting for table metadata lock | select count(*) from big_table where TABLE_SCHEMA=’test’ |
    +—-+——+———-+——+———+——+———————————+—————————————————————+
    4 rows in set (0.00 sec)
    小结7:lock=exclusive,不允许DML,查询并发。

    转载于:https://blog.51cto.com/5317968/1580976

    展开全文
  • --获取索引,约束(主键)的DDL declare @tabname varchar(50) set @tabname='testbzm'--表名if ( object_id('tempdb.dbo.#IDX') is not null) begin DROP TABLE #IDX DROP TABLE #IDX2 DROP TABLE #IDX3 endSELECT a....
  • to_char(dbms_metadata.get_ddl('CONSTRAINT', c1.constraint_name)) DDL_SQL  from user_constraints c,  user_constraints c1,  tmp_cons_cols a,  tmp_cons_cols a1  where c.constraint_...
  • --获取建表DDL(+主键) declare @tabname varchar(50) set @tabname='CSBILL'--表名 if ( object_id('tempdb.dbo.#t') is not null) begin DROP TABLE #t end select 'create table [' + so.name + '] (' + o.list ...
  • MySql之DDL操作创建表(添加主键, 外键约束以及基本的数据类型)
  • oracle 自动 导出 ddl
  • 在数据转换的过程中,我使用create table as ...等方式,批量将A的数据表创建到B下后, ...主键、外键、索引是需要手工建的,一个一个嫌麻烦,故写下了以下脚本。 可使A用户下的主键、外键、索引都产生出来,然...
  • 创建外键,声明主键的方式小结,mysql,主键,外键,数据类型,5.mysql 数据类型
  • 其实主键约束 相当于 UNIQUE+NOT NULL 一个表只允许有一个主键 主键所在列必须具有索引(主键的唯一约束通过索引来实现),如果不存在,将会在索引添加的时候自动创建 ​ 唯一性约束(UNIQUE)可以定义在表级或列级 ...
  • select 'CREATE TABLE [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name + ' PRIMARY KEY ' + ' ('...
  • 达梦修改主键

    千次阅读 2020-08-25 23:45:55
    达梦默认情况下主键为聚簇主键,后期需要修改主键比如从A列变为AB复合主键的话,可以通过随机找一个现有主键外的别的列创建一个聚簇索引,然后删除这个索引,这时候你就可以看到ddl主键从cluster primary key变成...
  • 前言 ´・ᴗ・` 组合主键 组合唯一键 上一节的问题 外键补充 添加约束 删除约束 修改约束 总结 ´◡`
  • MySQL 使用工具 Windows自带的命令行窗口 MySQL Front 一、MySQL常用数据类型 1. char 占用字节数 char(n) 定长字符串,存储控件大小固定 如:char(2) 2. varchar ...占用字节数 ...
  • 它支持mysql语法以及主键和外键约束。 安装 使用npm和npm install xml2ddl进行npm install xml2ddl 。 用法 xml2ddl导出一个函数,该函数采用文件名从中读取xml并使用sql命令数组调用回调。 var xml2ddl = require...
  • MySQL DDL

    2020-09-02 15:21:36
    摘要 MySQL生成表有两种方式,一种是通过工具,另一种是手写DDL(Data Definition Language)。通过工具设计表结构通常比较简单,... `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varch
  • StarUML的DDL扩展 StarUML的此扩展( )支持从ERD生成DDL(数据定义语言)。... 主键的转换如下: CREATE TABLE entity1 ( pk1 INTEGER , pk2 VARCHAR ( 10 ), ... PRIMARY KEY (pk1, pk2, ...) ); 非空列的转
  • oracle DDL

    2017-05-18 17:01:00
    一 改主键约束 3步 1.查询主键 select * from user_cons_columns c where c.table_name='表名'; 2.删除主键alter table 表名 drop constraint 主键名; 3.新建主键 alter table 表名 add constraint 主键名 ...
  • Hibernate封装了数据库DDL语句,只需要将数据表和类之间实现映射,即可对数据表进行操作。 示例:数据库中存在表interface_admin.ds_area,实现表和类之间映射,其中单一主键oggKeyId,使用主键自动生成策略...
  • 在一个表中的字段添加主键时,加了主键自增,在删除主键的时候发现报错了。查了资料得知,在删除有主键自增的主键时,需要将主键自增取消掉,才可以删除这张表的主键。 取消主键自增的方法: 利用DDL语句 alter ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 940
精华内容 376
关键字:

ddl主键