• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

the MySQL table defination table

Dear all,

when we create a MySQL table or add a new column, we might add the AFTER whne alter that table:

http://www.tech-recipes.com/rx/378/add-a-column-to-an-existing-mysql-table/

but any information schema table store this kind of AFTER information?
0
marrowyung
Asked:
marrowyung
  • 8
  • 5
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you are looking for this system view:
http://dev.mysql.com/doc/refman/5.0/en/columns-table.html
information_schema.columns

it contains the ordinal_position value that indicates in which order the columns are defined in the table.

however, you cannot find back the information if the column was added (after having created the table), and that the AFTER keyword was used ...
0
 
marrowyungAuthor Commented:
"however, you cannot find back the information if the column was added (after having created the table), and that the AFTER keyword was used ... "

that's what I am worrying about, I use the information_schema.columns like this"

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;

Open in new window


but it don't return information about before it added, which column it add after or it add at the end of the columns.

This is what you mean too ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes.

however, I think you may find something in the binlog, but I am not sure how to exploit it, I never did that:
http://dev.mysql.com/doc/refman/5.1/en/binary-log-mysql-database.html
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
marrowyungAuthor Commented:
binary log you mean? I don't think we can do it, right?
0
 
marrowyungAuthor Commented:
any command to check the bin log from workbench/Toad for MySQL ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the tool to "query" the binary log is the mysqlbinlog:
http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html
so, the answer is you cannot "query" that directly with a "sql"
0
 
marrowyungAuthor Commented:
ok.
0
 
marrowyungAuthor Commented:
then can't make sure of it, right? I want then manage it in MysQL logic
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, what is your goal here, actually?
I mean, why do you want to see/get that information?
what's the "project/big picture" behind this?
0
 
marrowyungAuthor Commented:
I am doing a data audit project that try to record the data change by the add/remove column of a table.

When we run a SP, the respective audit tables on a separate DB will be create for all BEFORE DELETE, AFTER UPDATE/INSERT data related event by trigger will fired.

So what if the developer add/remove fields and what if they do:

use yyyy;
ALTER TABLE wwwww
ADD AAA INT(10) NOT NULL AFTER FROM_CITY ;  

Open in new window


As the field are in order and this statement says AFTER the field FROM_CITY, if the respective table need to follow the same thing to add back to the same column, how can we know it AFTER it is added?

This is the point. However, development manager said they can BRIEF the developer to add field without the AFTER in anytime and the field order in the audit table doesn't matter.

So, do you have any other idea on how to solve it technically ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no way, I would say.
0
 
marrowyungAuthor Commented:
yeah, I think so
.
0
 
marrowyungAuthor Commented:
That's what I mean too !
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now