Solved

the MySQL table defination table

Posted on 2014-01-20
13
374 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
  • 8
  • 5
13 Comments
 
LVL 142

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 142

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

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 142

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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now