怎么样批量修改一个Mysql数据库中数据表的前缀？ 如何批量修改mysql数据库中表的名字
Mysql\u5982\u4f55\u5bf9\u4e00\u4e2a\u5217\u4e2d\u7684\u6570\u636e\u8fdb\u884c\u7edf\u4e00\u4fee\u6539\uff1fmysql\u5bf9\u4e00\u4e2a\u5217\u4e2d\u7684\u6570\u636e\u7edf\u4e00\u4fee\u6539\u76f4\u63a5\u7528update\u8bed\u53e5\u5373\u53ef\u3002
\u5de5\u5177\uff1amysql 5.6
\u6b65\u9aa4\uff1a
1\u3001mysql\u4e2d\u6709\u6570\u636e\u8868student\u5982\u4e0b\uff1a
2\u3001\u8981\u5c06\u8868\u4e2dscore\u90fd\u6539\u6210100\uff0c\u7528\u5982\u4e0b\u8bed\u53e5\uff1a
update student set score=100;3\u3001\u8fd0\u884c\u540e\u7ed3\u679c\uff08\u53ef\u4ee5\u770b\u5230score\u90fd\u53d8\u6210\u4e86100\uff0c\u66f4\u6539\u6210\u529f\uff09\uff1a
\u53ef\u4ee5\u8bd5\u8bd5\u4ee5\u4e0bSQL\u751f\u6210\u7684\u8868\u540d
SELECT CONCAT('rename table ',table_schema,'.',table_name,' TO ',table_schema,'.NEW',table_name,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='mydb'
php调用这个sql
select concat('rename table ',table_name,' to abc',right(table_name,14),';') from information_schema.tables where table_schema='dbname' and table_name like '%_category_tags'
生成的结果集如下：
+---------------------------------------------------------------------+
| concat('rename table ',table_name,' to abc',right(table_name,14),';') |
+---------------------------------------------------------------------+
| rename table 12_category_tags to abc_category_tags; |
| rename table t2_category_tags to abc_category_tags; |
| rename table df_category_tags to abc_category_tags; |
| rename table ts_category_tags to abc_category_tags; |
| rename table ttt_category_tags to abc_category_tags; |
| rename table u_category_tags to abc_category_tags; |
| rename table yu_category_tags to abc_category_tags; |
+---------------------------------------------------------------------+
这些是修改表名的一些sql，你把这些值保存在php数组中，然后循环调用
但是有个问题哦，表名不能都改成abc_category_tags的，要么就是在原有表名前追加abc，这样表名就不会重复了
有问题可以追问。
给你个方案：
php调用这个sql
select
concat('rename
table
',table_name,'
to
abc',right(table_name,14),';')
from
information_schema.tables
where
table_schema='dbname'
and
table_name
like
'%_category_tags'
生成的结果集如下：
+---------------------------------------------------------------------+
|
concat('rename
table
',table_name,'
to
abc',right(table_name,14),';')
|
+---------------------------------------------------------------------+
|
rename
table
12_category_tags
to
abc_category_tags;
|
|
rename
table
t2_category_tags
to
abc_category_tags;
|
|
rename
table
df_category_tags
to
abc_category_tags;
|
|
rename
table
ts_category_tags
to
abc_category_tags;
|
|
rename
table
ttt_category_tags
to
abc_category_tags;
|
|
rename
table
u_category_tags
to
abc_category_tags;
|
|
rename
table
yu_category_tags
to
abc_category_tags;
|
+---------------------------------------------------------------------+
这些是修改表名的一些sql，你把这些值保存在php数组中，然后循环调用
但是有个问题哦，表名不能都改成abc_category_tags的，要么就是在原有表名前追加abc，这样表名就不会重复了
有问题可以追问。
绛旓細mysql鐨椤圭洰锛岄渶瑕佽璁涓涓蹇熶笂杞界殑鏈哄埗 鏈鍚庣殑瑙ｅ喅鍔炴硶鏄埄鐢ㄤ簡mysql鐨勯澶勭悊璇彞鐨勭壒鎬у疄鐜扮殑 mysql鐨勯澶勭悊璇彞鏀寔澶氳鏁版嵁鐨勯澶勭悊锛屽嵆insertinto(columnName,columnName锛)values(?,?锛)(?,?锛)杩欐牱浣犲湪缁戝畾杈撳叆鍙傛暟鐨勬椂鍊欏彲浠ュ湪绋嬪簭閲屽皢鏁村紶琛ㄧ殑鏁版嵁閮界粦瀹氬ソ鐒跺悗璋冪敤涓娆℃墽琛屽氨鑳藉皢鏁村紶琛ㄧ殑鏁版嵁...
绛旓細Java mysql mybatis鎵归噺鏇存柊鏁版嵁搴擄紝閲囩敤浠ヤ笅鍐欐硶鍗冲彲鎵ц锛屼絾鏄暟鎹簱杩炴帴蹇呴』閰嶇疆锛&allowMultiQueries=true 渚嬪锛歫dbc:mysql://192.168.1.236:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true<update id="batchUpdate" parameterType="java.util.List"> <foreach collecti...
绛旓細update tablename set ID=ID*10+1000000 where ID>=311100000 and ID<311200000
绛旓細鎵归噺鎻掑叆锛屼簨鍔″懡浠ゃ1銆佸湪mysql鏁版嵁搴撲腑锛岄氳繃鎵归噺鎻掑叆鏁版嵁婧愬懡浠わ紝鍙互瀹炵幇1绉掑唴鍐欏叆1w鏉℃暟鎹殑鎿嶄綔銆2銆佸彲浠ラ氳繃浜嬪姟鍛戒护鍙互瀹炵幇1绉掑唴鍐欏叆1w鏉℃暟鎹殑鎿嶄綔銆
绛旓細update 琛ㄥ悕 set sex=2 where sex=1
绛旓細update files as b set issubmitjob='false' where issubmitjob like '%';
绛旓細鍏堟妸鍘熻〃涓璉D瀛楁鍒犻櫎銆傚啀瀵煎嚭鏁版嵁锛屽啀瀵煎叆灏監K浜 缁欎綘涓涓PHP绠鍗曠殑瀵煎叆浠ｇ爜,灏辨槸閭ｄ釜鎰忔濅簡 query = $db->query("SELECT * FROM 鍘熷瓧娈");while ($newTable = $db->fetch_array($query)) { db->query("INSERT INTO 鏂板瓧娈 (sNa) VALUES ($newTable['sNa'])");} ...
绛旓細update pb_threads set uid=1 where uid=0
绛旓細UPDATE 琛ㄥ悕 SET 鎵嬫満鍙风爜瀛楁 = CASE id WHEN 1 THEN '13854268952' WHEN 2 THEN '15972012367' WHEN 3 THEN '13348902574' ENDWHERE id IN (1,2,3)灏辨槸涓婇潰杩欎緥瀛愶紝 浣犱篃鍙互鍘荤綉涓婃壘銆傚緢澶氫緥瀛愮殑
绛旓細ExtendedProperties灞炴х敤鏉ュ瓨鍌ㄧ敤鎴疯嚜瀹氫箟鐨勪俊鎭,鍦ㄦ澶勫簲璇ユ槸鍚慏ataTable涓坊鍔犱簡SQL淇℃伅,鐢ㄦ潵瀛樺偍鍏蜂綋鐨勬煡璇,濡 table.ExtendedProperties.Add("SQL","select * from 琛ㄥ悕");浠ュ悗灏卞彲浠ョ敤table.ExtendedProperties["SQL"].ToString()鏉ヤ唬鏇垮叿浣撶殑鏌ヨ璇彞浜 ...
