MySQL – Create Function for Transactions Using Multiple Databases

functionsMySQL

As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have them go to the correct database as part of that single call?

BEGIN;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
           VALUES ('Lola', 'High', DEFAULT, DEFAULT, DEFAULT);
  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `charge`, `plea`, `sentence`, `create_time`, `update_time`, `hashID`)
           VALUES (000, 412056888, 0, NULL, NULL, NULL, DEFAULT, DEFAULT, LAST_INSERT_ID());
  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
           VALUES (1, 1, 1985, 34506028, 0, DEFAULT, DEFAULT, LAST_INSERT_ID());

COMMIT;

This might entail, for example, a function that inserts a value to a field only IF that field exists. For example, if the field "create_time" exists in all database.tables, then the value would be inserted in each of them. On the other hand, if "last_name" exists in only one of them, then it would be inserted only in that database.table.

I want a function to store which databases have which tables and use a set of fields that entails a union of all databases. Then when I insert values to fields, without me dictating which database gets what, the function should do that automatically.

Could I create such a function? How would I store it?

Best Answer

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for?

delimiter //
CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, _update_time datetime,  _hash_id varchar(32),
  _id_status int, _id int, _criminal_status int, _day int, _month int, _year int, _state_id int, _state_id_status int)
begin
  START TRANSACTION;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
    VALUES (_first_name, _last_name, _create_time, _update_time, _hash_id);

  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, _create_time, _update_time, LAST_INSERT_ID());

  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_day, _month, _year, _state_id, _state_id_status, _create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Example call:

call yourdb.yourprocname ('Lola', 'High', CURRENT_TIMESTAMP, ... etc)

Related Solutions

MySQL – Insert into View Derived from Table with Discriminator Field

You should do your INSERTs into the base table only

That way you can INSERT queries like these

insert into base_table (a, b, c, x) VALUES (null, 'xxx', 'xxx',1);
insert into base_table (b, c, x) VALUES ('xxx', 'xxx',2);

If you do INSERT queries like these

insert into base_table (a, b, c) VALUES (null, 'xxx', 'xxx');
insert into base_table (b, c) VALUES ('xxx', 'xxx');

then you should assign a default value of 1 for x in the table definition

create table base_table (
  a int unsigned auto_increment primary key,
  b char(10) default null,
  c char(10) default null,
  x tinyint unsigned default 1
);

Otherwise, you should only INSERT into base_table since there exists no mechanism for dynamically switching the default value of x within the definition of a view. The x column has no ability to be selected in the view (because you left it out of the SELECT clause). Consequently, it would definitely have no ability to be updated or assigned in the view.

Give it a Try !!!

Mysql – Is a update-only-once-row table worth sharding

Is there a problem? Or are you expecting to grow significantly? 10M/day = 120/second, which is high, but not necessarily the limit.

innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest. A value of 2 will give you a boost in performance.

Batching INSERTs is also a performance boost; however you may not be able to do so because of how your app works. Also combining statements into a transaction would help -- if practical.

What you have described of your app sounds like all the activity is concentrated near the 'end' of the one table. This implies that there is very little I/O (other than transaction logging, and that was what I was addressing above).

Back to your question...

Sharding across N servers will cut the I/O, locking, CPU, etc by a factor of nearly N.

The process that decides which shard to go to should be another machine, and the shards should be alone on each of the machines.

A problem with sharding to handle growth is what to do when you need more than N machines.