Question 168410 in Mysql Question posted in
The official MySQL documentation can be found here.

What are MySQL Column/Table Aliases?

Does MySQL have any aliasing capabilities for column/table names without specifying them in the query?

For example, Let’s pretend I have a very complex application talking to an equally complex schema that’s been touched by many hands over the years.

There are inconsistent names, such as: ExpiryDate and DateExpiry, and we wish to standardize the naming in the database, without changing the entire codebase at the same time because the complexity in ensuring the whole thing will work with the new name.

In other words, could we define 2 potential names for the same column (or table)?

That way any "old" queries yet to be updated can still work while all new queries can reference the new (actual) column name?

Like a symbolic link so to speak.

I can’t do this at the application layer as there are some raw SQL queries, and not everything goes through an ORM type thing.

Fake example, FAKE SYNTAX:

CREATE TABLE Tickets (
   TicketID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
   DateExpiry DATETIME, /*Actual Column*/
   COLUMN ALIAS (DateExpiry, ExpiryDate) /*Fake Syntax for creating an alias*/
)

And then these 2 will do the same thing:

INSERT INTO Tickets (DateExpiry) VALUES ("2023-05-15 23:59:59");
INSERT INTO Tickets (ExpiryDate) VALUES ("2023-05-15 23:59:59");

I’ve looked at the documentation, but can’t find anything. It’s difficult because searched for Aliases end up with the traditional definition of the term in the context of SQL.

Tags:

2

Answers


  1. 0

    You cannot define column aliases in table definition in MySQL. But you are able to use views and define ‘aliases’ there.

    E.g.:

    CREATE VIEW Tickets_View AS
SELECT
   TicketID,
   DateExpiry AS ExpiryDate,
   DateExpiry AS DateExpiry
FROM
   Tickets;

    In this view there are two columns ExpiryDate and DateExpiry which both point to the same column in Tickets table.

    You then need to change your application code to use Tickets_View instead of Tickets table.

    But this might be probably easier to change than all column references.

    Another approach is to change your application code to use only one name for particular column. This might be harder, but it might be a good investment regarding the quality of your application code.

    Login or Signup to reply.
  2. 0

    Just have both columns in the table and use triggers to sync them.

    CREATE TABLE tickets
(
   ticketid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
   dateexpiry DATETIME,
   expirydate DATETIME
);

    CREATE TRIGGER trg_tickets_ins_old_new
  BEFORE INSERT ON tickets FOR EACH ROW
BEGIN
  -- dateexpiry set, but expirydate not? Then copy dateexpiry to expirydate.
  IF new.dateexpiry IS NULL AND new.expirydate IS NOT NULL THEN
    SET new.dateexpiry = new.expirydate;
  END IF;

  -- expirydate set, but dateexpiry not? Then copy expirydate to dateexpiry.
  IF new.expirydate IS NULL AND new.dateexpiry IS NOT NULL THEN
    SET new.expirydate = new.dateexpiry;
  END IF;
END;//

    CREATE TRIGGER trg_tickets_upd_old_new
  BEFORE UPDATE ON tickets FOR EACH ROW
BEGIN
  -- dateexpiry changed? Then copy its new value to expirydate.
  IF NOT new.dateexpiry <=> old.dateexpiry THEN
    SET new.expirydate = new.dateexpiry;
  END IF;

  -- expirydate changed? Then copy its new value to dateexpiry.
  IF NOT new.expirydate <=> old.expirydate THEN
    SET new.dateexpiry = new.expirydate;
  END IF;
END;//
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search