MySQL数据库的一些修改操作


=Start=

缘由:

记录常用的MySQL修改操作,方便以后参考。

参考解答:
修改MySQL列的大小
ALTER TABLE `table_name` MODIFY `col_name` VARCHAR(10000);
或
ALTER TABLE `table_name` CHANGE COLUMN `col_name` `col_name` VARCHAR(10000);
修改MySQL表的索引名称
对于 MySQL 5.7 及以上的版本(可以直接修改索引名称):
ALTER TABLE tbl_name RENAME INDEX old_index_name TO new_index_name;

对于 MySQL 早期版本(需要先删除已有索引,然后新建索引):
ALTER TABLE tbl_name DROP INDEX old_index_name, ADD INDEX new_index_name (...);
参考链接:

=END=

,

《“MySQL数据库的一些修改操作”》 有 9 条评论

  1. MySQL在指定列之前或之后插入列 (Add a column to an existing MySQL table)
    http://blog.csdn.net/qq361301276/article/details/10194811
    http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/
    `
    #原始表结构
    CREATE TABLE contacts (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(40),
    birthdate DATE
    );

    # 向 contacts 表增加一列 email 类型为 varchar(60)
    ALTER TABLE contacts ADD email VARCHAR(60);

    # 向 contacts 表在 name 列后面增加一列 email 类型为 varchar(60)
    ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

    # 向 contacts 表增加一列 email 并放在第一列的位置上
    ALTER TABLE contacts ADD email VARCHAR(60) FIRST;
    `

  2. 若已存在,则更新;若不存在,则插入 (MySQL If Exists Then Update Else Insert)
    https://stackoverflow.com/questions/12639407/sql-if-exists-update-else-insert-syntax-error
    https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
    https://dba.stackexchange.com/questions/99120/if-exists-then-update-else-insert
    `
    create table machine(
    machine_id int not null primary key,
    machine_name varchar(50),
    reg_id int
    );

    insert into machine (machine_id, machine_name, reg_id) values (1, ‘my_machine’, 1);

    # 注意 VALUES(machine_name) 这里面的 machine_name 是列名
    insert into machine (machine_id, machine_name, reg_id) VALUES (1, ‘test_machine’, 1) ON DUPLICATE KEY UPDATE machine_name=VALUES(machine_name);
    `

  3. MySQL UPDATE语句一个“经典”的坑
    http://tech.dianwoda.com/2017/12/14/mysql-updateyu-ju-ge-jing-dian-de-keng/
    `
    // 只能更新第一个字段
    update apps set owner_code=’43212′ and owner_name=’李四’ where owner_code=’13245′ and owner_name=’张三’;

    // 小结:在一条UPDATE语句中,如果要更新多个字段,字段间不能使用“AND”,而应该用逗号分隔。
    update apps set owner_code=’43212′, owner_name=’李四’ where owner_code=’13245′ and owner_name=’张三’;
    `

  4. IPv6 使用及注意事项
    https://arstercz.com/ipv6-使用及注意事项/

    有效升级 MySQL 表的 ip 字段
    https://arstercz.com/有效升级-mysql-表的-ip-字段/
    http://www.tldp.org/HOWTO/pdf/Linux+IPv6-HOWTO.pdf
    `
    有效的 IPv6 地址的长度大概为 3 ~ 39, 所以要使字段能够存储 IPv6 地址, 需要修改字段长度为 varchar(39) :

    ::1 ~ 2001:0db8:1111:1111:1111:1111:1111:1111

    而 StackOverflow 上的有个回答显示有些特殊字段可能会使长度长于 39 而长至 46 个字节,因此,我直接申请了 varchar(50) 作为 IPv6 字段的存储列。

    alter table table_name modify ip varchar(50);
    `

  5. How to store IPv6-compatible address in a relational database
    https://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database
    `
    Note that the maximum length of a IPv6 address, including scope identifier, is 46 bytes as defined by INET6_ADDRSTRLEN in standard C headers. For Internet usage you should be able to ignore the zone identifier (%10, #eth0, etc), but just be aware when getaddrinfo returns a longer result than expected.
    `

    http://en.wikipedia.org/wiki/IPv6_address#Link-local_addresses_and_zone_indices
    http://en.wikipedia.org/wiki/Getaddrinfo

    Storing IPv6 Addresses in MySQL
    https://stackoverflow.com/questions/6964143/storing-ipv6-addresses-in-mysql
    `
    BINARY(16)
    `

  6. MySQL中如何快速删除大表的数据?
    `
    # 方法一,使用 delete from
    DELETE FROM table_name WHERE condition;
    # 方法二,使用 truncate
    TRUNCATE TABLE table_name;
    # 方法三,先drop掉原始表,然后再创建一个同名同结构的表
    DROP table_name
    CREATE TABLE table_name

    CREATE TABLE new_table LIKE table_name;
    RENAME TABLE table_name TO old_table, new_table TO table_name;
    DROP old_table;
    `
    Delete all the data from all tables
    https://dba.stackexchange.com/questions/74519/delete-all-the-data-from-all-tables

    delete all from table
    https://stackoverflow.com/questions/3000917/delete-all-from-table

    Fastest way to delete all the data in a large table
    https://stackoverflow.com/questions/64117/fastest-way-to-delete-all-the-data-in-a-large-table

回复 hi 取消回复

您的电子邮箱地址不会被公开。 必填项已用*标注