MySQL Views

MySQL में View क्या है?

View named query definition है जिसे SELECT में table की तरह reference कर सकते हैं। यह selected columns expose, expressions calculate, tables combine या aggregates को stable interface से present कर सकती है। Normal MySQL view separately refreshed materialized result नहीं; base data tables में रहता है।

Verified Orders Lab

DROP TABLE IF EXISTS orders_view_lab;
DROP TABLE IF EXISTS customers_view_lab;

CREATE TABLE customers_view_lab (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(80) NOT NULL,
  city VARCHAR(50) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE orders_view_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  internal_note VARCHAR(100),
  FOREIGN KEY (customer_id)
    REFERENCES customers_view_lab(customer_id)
) ENGINE = InnoDB;

INSERT INTO customers_view_lab VALUES
(101,'Asha','Khurja'),(102,'Bilal','Aligarh'),
(103,'Charu','Khurja'),(104,'Deepak','Noida');

INSERT INTO orders_view_lab VALUES
(1,101,'PAID','2026-08-01',1200,'Priority customer'),
(2,101,'PENDING','2026-08-05',500,'Call after 4 PM'),
(3,102,'PAID','2026-08-03',750,NULL),
(4,103,'CANCELLED','2026-08-04',300,'Refund requested'),
(5,101,'PAID','2026-08-10',1500,NULL),
(6,102,'PENDING','2026-08-11',900,NULL),
(7,104,'PAID','2026-08-12',2200,'Do not expose'),
(8,101,'PAID','2026-08-14',650,NULL);

Examples internal_note public views से बाहर रखते हैं। Column hiding उस interface से exposure घटाती है, पर security के लिए correct GRANTs और DEFINER management जरूरी हैं।

Simple View Create और Query

CREATE OR REPLACE VIEW paid_orders_v AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab
WHERE status = 'PAID';

SELECT order_id, order_date, total_amount
FROM paid_orders_v
WHERE customer_id = 101
ORDER BY order_date;

Outer WHERE और ORDER BY view query करते समय apply होते हैं। SELECT * की जगह explicit columns लें; MySQL creation पर definition freeze करता है, इसलिए later base columns automatically नहीं जुड़तीं।

CREATE VIEW customer_order_public_v
  (order_no, customer_no, ordered_on, amount)
AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab;

Explicit column list stable public contract देती है। Names unique और list count selected columns के बराबर होना चाहिए।

Join Views और Aggregate Views

CREATE OR REPLACE VIEW paid_order_details_v AS
SELECT o.order_id, c.customer_name, c.city,
       o.order_date, o.total_amount
FROM orders_view_lab AS o
JOIN customers_view_lab AS c
  ON c.customer_id = o.customer_id
WHERE o.status = 'PAID';

SELECT customer_name, order_date, total_amount
FROM paid_order_details_v
WHERE city = 'Khurja'
ORDER BY customer_name, order_date;

CREATE OR REPLACE VIEW customer_paid_summary_v AS
SELECT customer_id, COUNT(*) AS paid_orders,
       SUM(total_amount) AS paid_total
FROM orders_view_lab
WHERE status = 'PAID'
GROUP BY customer_id;

Aggregate view reporting के लिए useful है, पर nonupdatable क्योंकि rows का base rows से one-to-one map नहीं। Queryability और updatability अलग properties हैं।

Views Inspect, Replace, Alter और Drop

SHOW CREATE VIEW paid_orders_v;
SHOW FULL TABLES WHERE Table_type = 'VIEW';

SELECT TABLE_NAME, IS_UPDATABLE, DEFINER,
       SECURITY_TYPE, CHECK_OPTION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

CHECK TABLE paid_orders_v;

SHOW CREATE VIEW stored definition/security attributes दिखाता है। Referenced object remove होने जैसे broken dependencies में CHECK TABLE useful है।

ALTER VIEW paid_orders_v AS
SELECT order_id, customer_id, order_date, total_amount
FROM orders_view_lab
WHERE status = 'PAID' AND total_amount >= 500;

DROP VIEW IF EXISTS customer_paid_summary_v;

CREATE OR REPLACE deployment-friendly है, पर columns, types या semantics change करने से applications break हो सकती हैं। Definitions को version-controlled schema मानें।

UNDEFINED, MERGE और TEMPTABLE

CREATE ALGORITHM = MERGE VIEW khurja_customers_v AS
SELECT customer_id, customer_name, city
FROM customers_view_lab
WHERE city = 'Khurja';

Requested algorithm unconditional performance command नहीं। Some definitions merge नहीं हो सकतीं। Expanded plan के लिए EXPLAIN SELECT ... FROM view_name और real predicates/joins के अनुसार base indexes रखें।

DEFINER, INVOKER और Privileges

CREATE
SQL SECURITY DEFINER
VIEW order_public_v AS
SELECT order_id, customer_id, status,
       order_date, total_amount
FROM orders_view_lab;

SQL SECURITY DEFINER default है: underlying privileges definer account से check होते हैं। INVOKER में caller से। दोनों में caller को view पर appropriate privilege चाहिए।

• Controlled durable service account definer रखें; personal account avoid करें।
• Users को required view operations दें, unnecessary base access नहीं।
• Sensitive columns/rows definition में न रखें।
• View complete authorization, auditing और validation का substitute नहीं।
• Backup/migration में missing definers review करें।

Performance, Restrictions और Checklist

• View TEMPORARY table reference नहीं कर सकती; TEMPORARY view नहीं होती।
• Referenced objects exist चाहिए; drop होने पर view unusable हो सकती है।
• View पर trigger नहीं बनता।
• Outer ORDER BY हो तो view का ORDER BY ignored; deterministic order बाहर माँगें।
• View और outer query दोनों में LIMIT का combined effect undefined; avoid करें।
• Abstraction expensive joins/functions छिपा सकती है; plans inspect करें।

1. Narrow stable column contract define करें।
2. Security context/definer deliberately चुनें।
3. Least privilege दें।
4. Important outer queries EXPLAIN करें।
5. Base join/filter columns index करें।
6. Output, NULLs, duplicates, order test करें।
7. Writes से पहले updatability check करें।
8. Dependencies साथ deploy करें।

आगे updatable views, stored procedures और stored functions पढ़ें।

Official संदर्भ

• MySQL 8.4: CREATE VIEW Statement
• MySQL 8.4: View Processing Algorithms
• MySQL 8.4: Using Views

Creation syntax, frozen definitions, algorithms, restrictions और security context official MySQL 8.4 manual से verify किए गए हैं।