search
HomeDatabaseMysql Tutorialmysql-merge合并表_MySQL

bitsCN.com

mysql-merge合并表

 

[sql] 

注意:  

1  每个子表的结构必须一致,主表和子表的结构需要一致,  

  

2  每个子表的索引在merge表中都会存在,所以在merge表中不能根据该索引进行唯一性检索。  

  

3  子表需要是MyISAM引擎  

4  AUTO_INCREMENT 不会按照你所期望的方式工作。  

  

建表语句  

  

create table tablename(正常的字段)engine=merge insert_method=last  

 

  

insert_method:  

  

有两个值如下:  

LAST  如果你执行insert 指令来操作merge表时,插入操作会把数据添加到最后一个子表中。  

  

FIRST  同理,执行插入数据时会把数据添加到第一个子表中。  

  

例子:  

  

create table user1(  id int(10) not null auto_increment,    name varchar(50),  sex int(1),    primary key(id)  )engine=myisam charset=utf8;    create table user2(    id int(10) not null auto_increment,    name varchar(50),  sex int(10)    ,primary key(id)  )engine=myisam charset=utf8;    insert into user1 (name,sex) values('张三',0);    insert into user2 (name,sex) values('lisi',1);    mysql> select * from user1;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | 张三 |    0 |  +----+------+------+    mysql> select * from user2;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | lisi |    1 |  +----+------+------+    create table alluser(    id int(10) not null auto_increment,  name varchar(50),    sex int(10),  index(id)    )type=merge union=(user1,user2) insert_method=last;    mysql> select * from alluser;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | 张三 |    0 |  |  1 | lisi |    1 |  +----+------+------+    mysql> insert into alluser(name,sex) values('嘿嘿',0);    mysql> select * from user1;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | 张三 |    0 |  +----+------+------+  1 row in set (0.00 sec)    mysql> select * from user2;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | lisi |    1 |  |  2 | 嘿嘿 |    0 |  +----+------+------+  

 

  

2 rows in set (0.00 sec)  

//他把这条数据存入了user2表里是因为我们的insert_method的参数填写的是last  

  mysql> update alluser set sex=replace(sex,0,1) where id=2;  +----+------+------+  | id | name | sex  |  +----+------+------+  |  1 | 张三 |    0 |  |  1 | lisi |    1 |  |  2 | 嘿嘿 |    1 |  +----+------+------+  

 


bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL: BLOB and other no-sql storage, what are the differences?MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialAdding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Article

Hot Tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),