Solved

the MySQL table defination table

Posted on 2014-01-20
13
377 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

861 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

23 Experts available now in Live!

Get 1:1 Help Now