背景
现在我有这么一个需求:
数据库A的user表需要导入到数据库B的account表
user表字段:uid,username,email,password,regdate,salt
account表字段:id,name,email,password,type,salt
导入的字段只有username,email,password,salt,并且regdate需要符合某个条件
下面分几种情况来写sql,主要区分insert into和replace into
情况
导入的数据在B库的表中完全不存在
直接insert into就好,使用replace into效果一样
INSERT INTO `B`.`account`(NAME,PASSWORD,email,salt) SELECT username,PASSWORD,email,salt FROM `A`.`users` WHERE regdate>1418313600
导入的数据部分存在
数据部分存在为了区分需要在两个表添加唯一索引,这个唯一索引(UNIQUE)必须是导入的字段里面的值
我在两个表中分别为username和name字段添加唯一索引
直接替换
replace into在操作的时候如果两表数据重复(必须定义UNIQUE),会先删掉那一行(注意是一整行),然后再执行insert into
造成的后果就是,account表中的type字段如果原来有值,会直接删掉,并重新插入,即变成默认值了
REPLACE INTO `B`.`account`(NAME,PASSWORD,email,salt) SELECT username,PASSWORD,email,salt FROM `A`.`users` WHERE regdate>1418313600
忽略重复
用IGNORE关键字,如果遇到重复,不会操作当前行
INSERT IGNORE INTO `B`.`account`(NAME,PASSWORD,email,salt) SELECT username,PASSWORD,email,salt FROM `A`.`users` WHERE regdate>1418313600
部分更新
比如果只想更新password和salt字段,可以用ON DUPLICATE KEY UPDATE
INSERT INTO `B`.`u_account`(NAME,PASSWORD,email,salt) SELECT username,PASSWORD,email,salt FROM `A`.`bbs_ucenter_members` WHERE regdate>1418313600 ON DUPLICATE KEY UPDATE PASSWORD=VALUES(PASSWORD),salt=VALUES(salt)
总结
replace into比较暴力,网上也有说慎重使用的,最好用insert into代替
疑问
以上的操作是两个数据库在同一台服务器上的,直接一条sql就可以搞定
假如要导的两个库在不同的两个服务器上要怎么做啊?
目前暂时的办法:
将A库中的user表导入到B库所在的另一台服务器上,然后写一条sql搞定
或者查询出指定数据,导出sql,在sql中将insert into替换为insert ignore into(有局限性)
以上所述就是本文的全部内容了,希望大家能够喜欢。

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

Atom editor mac version download
The most popular open source editor

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Zend Studio 13.0.1
Powerful PHP integrated development environment

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
