?
Solved

the MySQL table defination table

Posted on 2014-01-20
13
Medium Priority
?
391 Views
Last Modified: 2014-01-22
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
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39793797
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39796006
"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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39796058
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 1

Author Comment

by:marrowyung
ID: 39796201
binary log you mean? I don't think we can do it, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39796254
any command to check the bin log from workbench/Toad for MySQL ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39796315
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39796525
ok.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39796527
then can't make sure of it, right? I want then manage it in MysQL logic
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39796546
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39798923
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39799393
no way, I would say.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39799422
yeah, I think so
.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39799423
That's what I mean too !
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question