Solved

the MySQL table defination table

Posted on 2014-01-20
13
388 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 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