Implementation of stack in MySQL

Question

I had an abstract idea of implementing a stack in MySQL. Tried a lot of Google work but couldn't reach to any logical conclusion.

The Stack must be a collection of tables with the stack top pointing at a particular table. We may have various such stacks in our DB. The stack length may increase if for example the field from the table is full. Likewise the stack length may decrease in the opposite manner!

Could you please give me an implementation idea! is it really possible at first place? Hope I'm clear with my question.

Answer 1

If you really need to do a stack in MySQL I think you should clarify your answer as to why. One key aspect of MySQL is that it makes a bunch of fundamental assumptions that it is serving to a single application's codebase. If you are on MySQL, the best thing to do is implement this application-side. If you really need to do this in the database, you need to rethink how you are using MySQL, or whether you want to use a different database. Treating SQL as a public API on MySQL is problematic because applications can set sql_mode which can do things like determine how invalid data is treated or even what counts as valid data, and the dba has no control over what sql mode an application is using.

This being said, a stack on a db might look like (assuming you are not doing composite keys):

CREATE TABLE stack_demo (
   id bigint autoincrement primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);

Or better yet, the PostgreSQL version:

CREATE TABLE stack_demo (
   id bigserial primary key,
   table_name text not null,
   key_name text not null,
   key_value text not null,
   nav_down bigint unique references stack_demo(id)
);