Hướng dẫn mysql copy indexes from one table to another - mysql sao chép chỉ mục từ bảng này sang bảng khác

Đây là bài viết cũ, nhưng tôi hình dung tôi có thể giúp đỡ người khác. Bạn có thể làm điều gì đó như thế này:

SELECT concat["ALTER TABLE ",trim[TABLE_NAME]],group_concat[IF[INDEX_NAME="PRIMARY", 
"ADD PRIMARY KEY "," ADD INDEX `"],INDEX_NAME,"` [`",column_name,"` ASC] "], ";" 
FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'SCHEMA' and table_name like "TABLE_NAME%" 
and seq_in_index=1 group by table_name;`

hoặc khác cho chỉ mục sao chép vào một bảng cho các trường khác có cùng trường:

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

Sau đó sao chép dán vào cửa sổ truy vấn.

Điều cơ bản

Để có được tất cả các định nghĩa chỉ số, bạn có thể sử dụng chức năng thông tin danh mục hệ thống pg_get_indexdef[index_oid] như @a_horse đã được cung cấp.pg_get_indexdef[index_oid] like @a_horse already provided.

Truy vấn có thể đơn giản hơn đáng kể, mặc dù, sử dụng tham số

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

0. Đối với một bảng gọi là

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

1 trong lược đồ

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

2:

SELECT pg_get_indexdef[indexrelid] || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

Điều này bao gồm tất cả các chỉ mục: PK, một phần, chức năng, duy nhất, với các lớp toán tử đặc biệt, v.v.

Chi tiết cho

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

0:

• SQL tiêm trong các chức năng postgres so với các truy vấn đã chuẩn bị

Chuẩn bị các báo cáo DDL

Bạn không thể làm việc với điều này, chưa. Bạn phải thay thế tên bảng cũ bằng tên mới - và bạn không muốn thay thế các dương tính giả trong chuỗi [như tên cột khớp với tên bảng]:

Nếu bạn đang làm việc với quy ước đặt tên mặc định của Postgres, các định nghĩa chỉ số trông như thế này:default naming convention of Postgres, index definitions look like this:

CREATE INDEX tbl_tbl_id_idx ON tbl USING btree [tbl_id];

CREATE INDEX tbl_people_gin_idx ON tbl
USING gin [[[data -> 'people'::text]] jsonb_path_ops];

CREATE INDEX tbl_comecol_nonull_idx ON tbl
USING btree [somecol] WHERE [cutblade IS NOT NULL];

Tôi in đậm trong mọi lần xuất hiện của tên bảng ban đầu. Lưu ý ba dương tính giả [một phần của tên cột hoặc tên chỉ mục] mà chúng tôi không muốn thay thế.

Truy vấn này sẽ hoạt động hoàn hảo - nhưng xác minh điều đó so với các chỉ mục thực tế của bạn!verify that against your actual indexes yourself!

SELECT regexp_replace[regexp_replace[
          pg_get_indexdef[indexrelid]
        , 'tbl', 'tbl1']
        , ' ON tbl ', ' ON tbl1 ']
    || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;

1ST

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

4 chỉ thay thế trận đấu đầu tiên trong tên [trừ khi được hướng dẫn khác].

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

5 thứ 2 cụ thể hơn và chỉ thay thế tên bảng thực tế.

Đối với các ví dụ đã cho bạn nhận được:

CREATE INDEX tbl1_tbl_id_idx ON tbl1 USING btree [tbl_id];

CREATE INDEX tbl1_people_gin_idx ON tbl1
USING gin [[[data -> 'people'::text]] jsonb_path_ops];

CREATE INDEX tbl1_somecol_nonull_idx ON tbl1
USING btree [somecol] WHERE [cutblade IS NOT NULL];

Nhưng chúng tôi chưa xem xét các tên bảng, lược đồ hoặc cài đặt

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

6. Tất cả điều đó được xây dựng vào chức năng dưới đây.

Tự động hóa đầy đủ

Nếu bạn tự tin vào sơ đồ đặt tên của mình, bạn có thể hoàn toàn tự động hóa:fully automate:

CREATE OR REPLACE FUNCTION f_copy_idx[_tbl text, _tbl1 text
                                    , _sch text = 'public', _sch2 text = 'public']
  RETURNS void AS
$func$
DECLARE
   _full_tbl  text := format['%I.%I', _sch, _tbl];
   _full_tbl1 text := format['%I.%I', _sch2, _tbl1];
BEGIN
   -- RAISE NOTICE '%', -- for testing
   EXECUTE
   [SELECT string_agg[
              regexp_replace[regexp_replace[
                 pg_get_indexdef[indexrelid]
               , _tbl,  _tbl1]
               , ' ON ' || _full_tbl || ' ',  ' ON ' || _full_tbl1 || ' ']
            , '; '] AS idx
    FROM   pg_index
    WHERE  indrelid = _full_tbl::regclass];
END
$func$ LANGUAGE plpgsql SET search_path = '';

Điều này cũng được chuẩn bị cho các tên bảng không chuẩn cần phải trình chạy hai và khác nhau.

Để buộc các tên bảng đủ điều kiện lược đồ, tôi đặt lại search_path bên trong hàm [

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

7]. Bằng cách này, tôi nhận được kết quả đáng tin cậy không phụ thuộc vào cài đặt.

Tôi đã thêm

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

8 làm giá trị mặc định cho các tham số lược đồ

SELECT concat["ALTER TABLE ",trim[b.TABLE_NAME]],
group_concat[IF[a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"],
a.INDEX_NAME,"` [`",a.column_name,"` ASC] "], ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

9 và

SELECT pg_get_indexdef[indexrelid] || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

0. Vì vậy, nếu cả hai bảng nằm trong lược đồ công khai, bạn có thể đơn giản bỏ qua các tham số lược đồ.

Call:

SELECT f_copy_idx['mytbl', 'newtbl'];  -- all in schema public

Hoặc cho các tên bảng yêu cầu các lược đồ hai lần và các lược đồ khác nhau:

SELECT f_copy_idx['old_TBL', 'table', 'public', 'New_SCHEmA'];

Fiddle SQL thể hiện chức năng đang sử dụng. demonstrating the function in use.

Làm cách nào để sao chép một chỉ mục từ bảng này sang bảng khác?

Làm cách nào để sao chép bảng từ bảng này sang bảng khác trong MySQL?

Làm cách nào để sao chép dữ liệu từ bảng này sang bảng khác trong SQL?

Làm cách nào để sao chép cột từ bảng này sang bảng khác trong MySQL?