Geek asks and answers

Automatically add MySQL between tables

In MySQL, is it possible to have a column in two different tables that grow automatically? Example: table1 has a column "secondaryid", and table2 also has a column "secondaryid". Is it possible for table1.secondaryid and table2.secondaryid to keep the same information? Like table1.secondaryid may contain values ​​1, 2, 4, 6, 7, 8, etc. And can table 2.sondondid contain values ​​3, 5, 9, 10? The reason for this is twofold: 1) the two tables will be indicated in a separate “likes” table (similar to users who love the page on facebook), and 2) the data in table 2 are subsets of table1 using the primary key. Thus, the information posted in table 2 depends on table 1, since they are topics of different categories. (categories are table1 and topics are table2). Is it possible to do somethingdescribed above, or is there some other structural work that I don't know about?

+5
source share
7 answers

It seems that you want to distinguish between categories and topics in two separate tables, but there are identifiers for both of them in another table likesto make it easier for users to like a category or topic.

What you can do is create a super-entity table with categoriesand subtypes topics. A key with auto-increments will be generated in the super-entity table and inserted into only one of the two subtype tables (depending on whether it is a category or topic).

Subtype tables reference this super entity through an automatically increasing field in a 1: 1 ratio.

, - likes ( ), id .

, :

ER Model

, , superentity.

- . , , categories topics title url: superentity, . , .

+4

auto_increment, , :

set @@auto_increment_increment=2; // change autoinrement to increase by 2

create table evens (
    id int auto_increment primary key
);
alter table evens auto_increment = 0;

create table odds (
    id int auto_increment primary key
);
alter table odds auto_increment = 1;

, , auto_inc 2 1.

+4

, , table2 AUTO_INCREMENT .

ALTER TABLE `table2` AUTO_INCREMENT=1000000000;
+3

, mysql sql:

ALTER TABLE users AUTO_INCREMENT = 3

, 1 , auto increment .

0

:

-- see http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
CREATE TABLE sequence (id INT NOT NULL);
INSERT INTO sequence VALUES (0);

CREATE TABLE table1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    secondardid INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE TABLE table2 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    secondardid INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

DROP TRIGGER IF EXISTS table1_before_insert;
DROP TRIGGER IF EXISTS table2_before_insert;

DELIMITER //

CREATE
TRIGGER table1_before_insert
    BEFORE INSERT ON 
        table1
    FOR EACH ROW
BEGIN
    UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    NEW.secondardid = LAST_INSERT_ID();
END;
//

CREATE
TRIGGER table2_before_insert
    BEFORE INSERT ON 
        table2
    FOR EACH ROW
BEGIN
    UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    NEW.secondardid = LAST_INSERT_ID();
END;
//
0

. 2 3, . , 2 3?

, , . , . -, , . , , , .

-, "" . " " . , , . , "" "id". , "", . " a.type = b.type a.id = b.id". ( "" , ).

0

, MySQL, , PosgreSQL. , (), , . , , , .

, . , DB PEAR module, .

0

More articles:


All Articles