How to modify the table prefix in mysql

In mysql, you can use the "ALTER TABLE" statement to modify the table prefix. Modifying the table prefix can be regarded as modifying the table name. You can use RENAME to modify the table name. The syntax is "ALTER TABLE original table name RENAME TO new table name;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to modify the table prefix in mysql

Use sql statement to modify the mysql database table prefix name

The first thing we think of is to use sql query statement to modify it. This method is also very convenient. Just enter the following language name in the Run SQL query box.

ALTER TABLE 原表名 RENAME TO 新表名;

For example:

ALTER TABLE old_post RENAME TO new_post;

Sql query statement has a disadvantage, that is, one SQL statement can only modify the table name of one database. If you want to modify a certain table accurately, that's fine. use. If there are many database tables, it will be more troublesome.

But we can generate all sql statements at once through one statement:

select concat('alter table ',table_name,' rename to ',table_name) from information_schema.tables where table_name like'dmsck_%';

The generated statement is as follows:

alter table dmsck_acategory rename to dmsck_acategory; alter table dmsck_address rename to dmsck_address; alter table dmsck_article rename to dmsck_article; alter table dmsck_attrcategory rename to dmsck_attrcategory; alter table dmsck_attribute rename to dmsck_attribute; alter table dmsck_brand rename to dmsck_brand; alter table dmsck_cart rename to dmsck_cart; alter table dmsck_category_attr rename to dmsck_category_attr; alter table dmsck_category_goods rename to dmsck_category_goods; alter table dmsck_category_store rename to dmsck_category_store; alter table dmsck_collect rename to dmsck_collect; alter table dmsck_comment rename to dmsck_comment; alter table dmsck_coupon rename to dmsck_coupon; alter table dmsck_coupon_sn rename to dmsck_coupon_sn; alter table dmsck_enterprise rename to dmsck_enterprise; alter table dmsck_filmstrip rename to dmsck_filmstrip; alter table dmsck_friend rename to dmsck_friend; alter table dmsck_function rename to dmsck_function; alter table dmsck_gattribute_rule rename to dmsck_gattribute_rule; alter table dmsck_gcategory rename to dmsck_gcategory; alter table dmsck_goods rename to dmsck_goods; alter table dmsck_goods_attr rename to dmsck_goods_attr; alter table dmsck_goods_bak rename to dmsck_goods_bak; alter table dmsck_goods_down_log rename to dmsck_goods_down_log; alter table dmsck_goods_image rename to dmsck_goods_image; alter table dmsck_goods_old rename to dmsck_goods_old; alter table dmsck_goods_qa rename to dmsck_goods_qa; alter table dmsck_goods_spec rename to dmsck_goods_spec; alter table dmsck_goods_statistics rename to dmsck_goods_statistics; alter table dmsck_groupbuy rename to dmsck_groupbuy; alter table dmsck_groupbuy_log rename to dmsck_groupbuy_log; alter table dmsck_keyword rename to dmsck_keyword; alter table dmsck_mail_queue rename to dmsck_mail_queue; alter table dmsck_member rename to dmsck_member; alter table dmsck_message rename to dmsck_message; alter table dmsck_module rename to dmsck_module;

Then copy it to a text file, which will be modified Modify the prefix uniformly (a little troublesome), and then copy it to mysql to execute the sql statement and it will be ok.

Recommended learning:mysql video tutorial

The above is the detailed content of How to modify the table prefix in mysql. For more information, please follow other related articles on the PHP Chinese website!