Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • 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
 
marrowyungSenior Technical architecture (Data)Author 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
marrowyungSenior Technical architecture (Data)Author Commented:
binary log you mean? I don't think we can do it, right?
0
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok.
0
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, I think so
.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
That's what I mean too !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

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.

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