我一直在阅读 MariaDB 手册和教程,显然这应该有效:
EXPLAIN CREATE TABLE `prefix_database2`.`table1` LIKE `prefix_database1`.`table1`;`
该LIKE
运算符应该复制表的列和键。我不知道为什么会出现语法错误。
如何从第一个数据库复制第二个数据库中的表,同时保持完全相同的列、键等?
我一直在阅读 MariaDB 手册和教程,显然这应该有效:
EXPLAIN CREATE TABLE `prefix_database2`.`table1` LIKE `prefix_database1`.`table1`;`
该LIKE
运算符应该复制表的列和键。我不知道为什么会出现语法错误。
如何从第一个数据库复制第二个数据库中的表,同时保持完全相同的列、键等?
example
@ 。localhost
UPDATE information_schema.TABLES ...
我在两台服务器上运行了完全相同的 SQL 查询( )。拒绝用户“example”@“localhost”访问数据库“information_schema”
SELECT * FROM mysql.global_priv WHERE 'User'='example';
。mysql.db
数据库/表中没有提及example
用户,因为它再次具有全局权限。mysql.db
数据库/表中没有提及information_schema
任何服务器上的数据库。我如何强制 MariaDB 允许用户在实时服务器上example
执行我的查询?UPDATE information_schema.TABLES ...
我有一个数据库,由于服务器迁移导致的排序问题而被拆分。
我有一个merge1
数据库,它是没有损坏数据的旧数据库,但它缺少较新的条目。
我拥有merge2
包含所有条目(旧的和新的)的数据库,但是许多旧的条目已损坏(新的条目没有问题)。
两个数据库都在完全相同的 MariaDB 数据库服务器上,我已经知道如何执行数据库选择器之类的操作(merge1.table3
)。
root
我正在使用不向公众公开的MariaDB用户,因为我认为这纯粹只是内部维护。
这两个数据库之间绝对没有数据库结构差异,并且在数据库合并完成之前不会发生任何结构变化,目前还没有任何计划。
information_schema.COLUMNS
到目前为止,我已经对数据库和表(TABLE_NAME
和)完成了大量工作,COLUMN_NAME
因此我有每个表的所有行的列表:
SELECT `COLUMN_NAME` FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA`='merge1';
既然“大局”已经确立,我正在尝试确定是否以及如何通过动态构建查询来简化工作,因为有很多表,有些表有很多列。因此,假设表table3
有x
多个列(两列、几十列等。我已经知道该merge1
表有 200 个基本条目,所以我只需要INSERT
201+ 个(一个静态示例,以保持简单)。
以下是我粗略的非工作示例:
INSERT INTO merge1.table3
(
SELECT `COLUMN_NAME`
FROM information_schema.`COLUMNS` WHERE `TABLE_SCHEMA`='merge1'
)
VALUES
(
SELECT * FROM merge2.table3 information_schema WHERE id>200
);
问题似乎根源于插入的值,因为 MariaDB 似乎满足于SELECT
提供列名的子集。也许我没有动态构建查询的后半部分?
如何修复/改进查询以提供匹配的列名,而不必被迫静态定义它们?
我进行了服务器迁移,当我将数据库从旧服务器移植到新服务器时,出现了一些数据损坏。例如,IPv6 二进制数据看起来完全相同,但旧服务器的条目通常返回为空。在新服务器上创建的记录很好。数据库结构没有任何变化。
如何从旧服务器获取原始导出数据并从新服务器的数据库中导入新记录?
我进行了服务器迁移并进行了审计。在新服务器上,我终于可以自由地将 MariaDB 升级到 10.11(需要 10.10 才能获得 Unicode 14.0 支持),之前一直停留在 Unicode 5.2 支持。
我已经通过 PHP 跟踪了这个问题print_r(mysqli_get_charset($db));
。更改服务器排序规则 ( SHOW VARIABLES LIKE '%coll%';
) 后,所有内容都正确显示为utf8mb4_uca1400_ai_ci
。我尚未更新数据库或表,它们utf8mb4_unicode_520_ci
目前都还好。
问题是print_r(mysqli_get_charset($db));
返回utf8mb4_general_ci
。因此,我通过从本地 MariaDB 安装开始进行调查,因为我正在运行所有查询日志。当您在 PHP 中运行该命令时,它会转换为以下查询:
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='__database_name_here__';
好的,我接受了该查询并在实时服务器上运行它并得到结果utf8mb4_unicode_520_ci
(再次,目前一切正常)。出于不相关的原因,我多次重启服务器,因此这不应该是“卡在内存中”的问题。
那么从哪里mysqli_get_charset($db)
获取utf8mb4_general_ci
排序规则以便我可以更新它?
我遇到过有人建议使用单个查询来使用多行ON DUPLICATE KEY UPDATE
。UPDATE
不幸的是,似乎没有人使用干净的 SQL(定义一个明显的主键id
并使用至少两行),因此我无法确认效果令人满意。
假设表中的所有数据都存在并且某些quantity
行会有所不同:
INSERT INTO variations (id, name, quantity)
VALUES
(1, 'John', 4),
(2, 'Amy', 5),
(3, 'Elizabeth', 6)
ON DUPLICATE KEY UPDATE
id=VALUES(id),
name=VALUES(name),
quantity=VALUES(quantity);
如果表中的数量是 1、2 和 3,那么我的构成是上面的查询应该UPDATE
是 4、5 和 6。但是手动运行命令返回Affected rows: 0
并且我的应用程序中的数据毫无疑问不会改变.
说明:
name
行的值可能会发生变化,我试图保持代码简单。id
键,没有其他键。UPDATE
不必发生。id
可能更改。VALUES()
它是否使用$row
(例如来自 PHP)数据以及UPDATE
它是否与行中已有的数据不同,没有任何说明。我很想将代码转储到一个循环中以每行UPDATE
运行一个查询,因为我没有时间从中创建一个巨大的项目。这里或 SO 中的现有线程都没有正确解决多个 UPDATE 单查询问题(使用看起来像可怕的噩梦来构造的示例),所以我希望得到一个可以毫无疑问地澄清这一点的答案,以便我可以从中学习令人沮丧的挑战。case
我有两列,material_size
(text
类型)和material_size_temp
(text[]
数组类型)。
当我跑步时,UPDATE parts SET material_size_temp[0] = material_size;
我会得到像[0:0]={".021 x 2.450"}
而不是这样的值{".021 x 2.450"}
。
如何将text
类型列克隆到text[]
数组类型列?
如何使用字符串和DECLARE
变量的组合动态引用序列名称(如下所示)?下面的代码是正确的还是有另一种方法可以做到这一点?
出于性能原因,我的目标是在DO
/BEGIN
块内执行此操作,而且它会通过做一些有用的事情来帮助我理解 plpgsql,所以除非我真的必须这样做,否则我不会将它混入 PHP 中。
DO $$
DECLARE PKEY VARCHAR;
BEGIN
SELECT pg_attribute.attname INTO PKEY
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'parts1'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;
--SELECT setval('parts1_id_seq', (SELECT MAX(pkey) + 1 FROM parts));
SELECT setval('parts1_' || PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));
END;
$$ LANGUAGE plpgsql;
所以我运行这个查询:
SELECT f_copy_tbl('parts','parts1');
...在根据此答案处理代码时,我收到以下错误消息:
错误:关系“parts1”已经存在
pgAdmin III 没有显示表格(我显然在单击正确的项目后按 F5 重新加载了左列,因为它具有级联重新加载效果)。只是为了覆盖我的基础,我运行以下查询:
DROP TABLE IF EXISTS parts1 RESTRICT;
... psql 返回:
注意:表“parts1”不存在,正在跳过
那么,如果不是表格,那么参考是什么?parts1
多少查询?所有查询:
CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
RETURNS void AS
$func$
BEGIN
-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);
-- Fix serial columns, if any
EXECUTE (
SELECT concat_ws(E'\n'
, string_agg('CREATE SEQUENCE ' || seq, E';\n') || ';'
, string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I;'
, seq, _newtbl, a.attname), E'\n')
, 'ALTER TABLE ' || quote_ident(_newtbl)
, string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
, a.attname, seq), E',\n')
)
FROM pg_attribute a
JOIN pg_attrdef ad ON ad.adrelid = a.attrelid
AND ad.adnum = a.attnum
, quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq -- new seq name
WHERE a.attrelid = _tbl
AND a.attnum > 0
AND NOT a.attisdropped
AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND ad.adsrc = 'nextval('''
|| (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
|| '''::regclass)'
);
--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');
INSERT INTO parts1 SELECT * FROM parts;
--Now get primary key so we can fix the sequence...
--DECLARE PKEY VARCHAR;
--SELECT pg_attribute.attname INTO PKEY FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'parts1'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary;
--Now
--SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));
/**********
--Now get highest number of primary key *AND* increment it...
--DECLARE PKEY_NUMBER INTEGER;
--SELECT (PKEY + 1) INTO PKEY_NUMBER FROM parts1 ORDER BY PKEY DESC LIMIT 1;
--Fix primary key's autoincrement...
--ALTER SEQUENCE parts1_id_seq RESTART WITH PKEY_NUMBER;
*********/
END
$func$ LANGUAGE plpgsql VOLATILE;
在创建列管理工具时,我遇到了在 PostgreSQL 中快速复制表的需求,因此我不会使用非测试表测试新工具。为了有效地测试我最终打算在表格上使用的新列工具,parts
我创建了这个新工具来复制parts
,所以我最终会得到一个parts1
表格。当我以为我终于解决了所有问题时,当列工具删除表时遇到以下错误:
错误:无法删除表部分,因为其他对象依赖于它详细信息:表部分的默认值列 id 取决于序列parts_id_seq1
我花了我一天的大部分时间来研究这个解决方案,所以简而言之,我可以简单地使用字符串函数来重命名SEQUENCE_NAME
变量以将parts
表与parts
表分离,还是比这更复杂的问题?这是查询:
DO $$
DECLARE
SEQUENCE_NAME VARCHAR;
BEGIN
SELECT s.relname INTO SEQUENCE_NAME
FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
AND t.relname='parts';
LOCK TABLE parts;
CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
INSERT INTO parts1 SELECT * FROM parts;
PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
END;
$$ LANGUAGE plpgsql;
我浏览了9.3 的文档,没有发现任何表明我可以或不能在SELECT
查询中使用来自查询的数据的信息BEGIN
。有可能还是我必须求助于某种WITH
查询?
我试图将两个查询合并到一个BEGIN
查询中,以找出在对数据库执行查询时如何更高效。我有另一个项目,这只兔子从我构建了一个快速复制表的工具的地方开始(这样我就可以测试我正在构建的其他数据库工具而不是原始工具)但是它不会复制主键。
第一个查询获取sequence
表的名称:
SELECT s.relname AS sequence
FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
AND t.relname='parts';
第二个查询复制了一个表,尽管它已经包含sequence
从第一个查询中获取的名称:
BEGIN;
LOCK TABLE parts;
CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
INSERT INTO parts1 SELECT * FROM parts;
SELECT setval('parts_id_seq1', (SELECT max(id) FROM parts)+1);
COMMIT;
我有一个有点复杂的场景,我用主键创建的测试表不会返回主键。pgAdmin III 报告没有限制。我有整个 PostgreSQL 查询日志,下面有我用来创建测试表的查询。然后我将主键放在另一个测试表上并使用生成的查询(这不是我手动运行的查询)来帮助我搜索 pgAdmin III 将主键放在有问题的表上但没有找到任何搜索:
ALTER TABLE public.delete_key_bigserial DROP CONSTRAINT
字符串“DROP CONSTRAINT”仅在可追溯到 2014 年 12 月 2 日的查询日志中出现一次,这比我创建测试表还早几周。我现在明白,主键可能会或可能不会被设置为bigserial
或serial
什至创建了一个没有主键的表,将 id 设置为整数,然后设置id
为主键(另一天的蠕虫病毒)。
在较早的问题中,我询问了如何获取data_type
包含的内容,bigserial
或者serial
Erwin Brandstetter 对此有很好的回答。他特别提供了两个查询,一个用于获取data_type
所有列的 s,一个用于获取data_type
主键的 。不幸的是,我一直在测试的其中一个测试表没有返回任何结果。
SELECT a.attrelid::regclass::text, a.attname,
CASE a.atttypid
WHEN 'int'::regtype THEN 'serial'
WHEN 'int8'::regtype THEN 'bigserial'
WHEN 'int2'::regtype THEN 'smallserial'
END AS serial_type
FROM pg_attribute a
JOIN pg_constraint c ON c.conrelid = a.attrelid AND c.conkey[1] = a.attnum
JOIN pg_attrdef ad ON ad.adrelid = a.attrelid
AND ad.adnum = a.attnum
WHERE a.attrelid = 'delete_key_bigserial'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
AND a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND c.contype = 'p' -- PK
AND array_length(c.conkey, 1) = 1 -- single column
AND ad.adsrc = 'nextval('''
|| (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
|| '''::regclass)';
该查询适用于所有其他表。
我从 2014 年 11 月开始才开始使用 PostgreSQL,大约从 2011 年开始使用 MySQL,所以我能做的最好的事情 AFAIK 就是尽可能多地获取相关数据。这是用于delete_key_bigserial
从查询日志创建表的查询:
CREATE TABLE public.delete_key_bigserial (id bigserial PRIMARY KEY NOT NULL)
WITH (OIDS = FALSE);
我简化了 Erwin 的查询并在表上使用它来将我的查询工具中的结果与查询在(所有四个上)上运行良好的不同测试表进行比较data_type
:
SELECT * FROM pg_attribute a
WHERE a.attrelid = 'delete_key_bigserial'::regclass
AND a.attnum > 0
AND NOT a.attisdropped
AND attname='id'
ORDER BY a.attnum;
+----------+---------+----------+---------------+--------+--------+----------+-------------+
| attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff |
+----------+---------+----------+---------------+--------+--------+----------+-------------+
| 46390 | id | 20 | -1 | 8 | 20 | 0 | -1 |
+----------+---------+----------+---------------+--------+--------+----------+-------------+
+-----------+----------+------------+----------+------------+-----------+--------------+
| atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | attisdropped |
+-----------+----------+------------+----------+------------+-----------+--------------+
| -1 | f | p | d | t | t | f |
+-----------+----------+------------+----------+------------+-----------+--------------+
+------------+-------------+--------------+--------+------------+---------------+
| attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions |
+------------+-------------+--------------+--------+------------+---------------+
| t | 0 | | | | |
+------------+-------------+--------------+--------+------------+---------------+
atttypid
当满足其他条件时,Erwin 通过列派生类型,但是生成的列/行与其他有效的表相同。我在尝试确定data_type
主键是什么时使用了另一个目录表,因此我决定也通过以下查询比较该表的结果:
SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='delete_key_bigserial'
AND is_nullable='NO';
返回的任何返回的列/行(除了table_name
和column_default
列中的表名)的唯一区别是dtd_identifier
列。该表delete_key_bigserial
返回dtd_identifier
具有值的列20
,对于查询返回的工作表1
。PostgreSQL element_types 文档的(底部)将该列描述为:
元素的数据类型描述符的标识符。这目前没有用。
我猜这是一种已弃用/过时的方式,虽然它可以简单地引用描述本身,但仍用于遗留目的?我不确定,但这就是我所在的位置,我什至不确定我是否走在正确的道路上。
我宁愿处理这个问题并从场景中学习,然后仅仅因为它是一个测试表而忽略它,因为有一天我确定当它不是测试表时我将不得不处理这个问题。我很乐意用相关信息更新我的问题,这些信息可能有助于查明问题所在。
在基于PostgreSQL wiki页面创建新表时,我已经组合了一种方法来确定您在语法data_type
中使用的内容。data_type
如果我的查询有问题,我实际上需要知道在给定场景中什么会在一个或多个查询在纯测试数据库/表上运行以修改该数据库/表的显式上下文中抛出它,所以运行这个查询以测试任何误报。
SELECT pg_attribute.attname,
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute
WHERE pg_class.oid = 'delete2'::regclass
AND indrelid = pg_class.oid
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = any(pg_index.indkey)
AND indisprimary;
这是一个带有主键的表,该表不使用此查询返回主键:
CREATE TABLE delete_key_bigserial (
test1 integer,
id bigserial NOT NULL,
col1 text,
col2 text,
test2 integer
);
我遇到了一个奇怪的地方,PostgreSQL在表中设置了data_type
to 。我做了以下查询以获得一些见解:ARRAY
information_schema.columns
SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';
我注意到该udt_name
列,想知道它是否是确定列数据是什么类型的数组的可靠方法?
SELECT column_name, data_type, udt_name FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
AND table_name='__table_name__';
我目前正在学习 PostgreSQL 和 MySQL 之间的区别,因为我有一个新项目,并且我还打算将我现有的软件从 MySQL 迁移到 PostgreSQL。我实际上已经开始创建一个 HTML 表,其中比较了两者之间的命令(用于用户/数据库/命令提示符等)。在阅读了此处的答案后,我注意到这role
似乎被用作一个群体。使用 MySQL,我有两个用户,基本上是公共的(DELETE
、、INSERT
和权限)SELECT
和UPDATE
一个具有几个额外权限的管理员用户。
所以基本上在 Windows 7 命令提示符下(仅限本地开发)......
GRANT
或REVOKE
获得用户权限时如何与用户进行比较?